I asked a question a few days ago (Access to SQL Server 2005 from a non-domain machine using Windows authentication) which got some interesting, but not usable suggestions. I'd like to ask the question again, but make clear what my constraints are:
I have a Windows domain within which a machine is running SQL Server 2005 and which is configured to support only Windows authentication. I would like to run a C# client application on a machine on the same network, but which is NOT on the domain, and access a database on the SQL Server 2005 instance.
I CANNOT create or modify OS or SQL Server users on either machine, and I CANNOT make any changes to permissions or impersonation, and I CANNOT make use of runas.
I know that I can write Perl and Java applications that can connect to the SQL Server database using only these four parameters: server name, database name, username (in the form domain\user), and password.
In C# I have tried various things around:
string connectionString = "Data Source=server;Initial Catalog=database;User Id=domain\user;Password=password";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
and tried setting integrated security to true and false, but nothing seems to work. Is what I am trying to do simply impossible in C#?
Thanks for any help, Martin
Best Answer
I had a similar problem where I was writing a tool that needed to run on a machine on one domain and authenticate with a SQL server on another domain using a trusted connection. Everything I could find on the subject said it couldn't be done. Instead you must join the domain, use SQL authentication, get involved with some chap called Kerberos, or get your network guys to setup a trusted relationship, to name a few alternatives.
The thing is I knew I could get it working in some way using RUNAS because I'd proven it with SSMS:
The /netonly flag allowed me to execute the exe with the local credentials and access the network with the remote credentials, I think, anyway I got the result set I expected from the remote server. The problem was the runas command made it very difficult to debug the application, and it didn't smell good.
Eventually I found this article on the code project which was talking about authenticating to manipulate Active Directory, Here is the main class that does the impersonation:
To use it just:
I added in the Undo method otherwise the impersonator object tended to get garbage collected. I also altered the code to use LOGON32_LOGON_NEW_CREDENTIALS but this was a poke and run to make it work; I still need to understand fully what it does, I have a feeling its the same as the /netonly flag on runas. I'm also going to break down the constructor a bit.