C# connect to domain SQL Server 2005 from non-domain machine

Question

Hi, I asked a question a few days ago (http://stackoverflow.com/questions/2795723/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

Answer 1

You have to configure SQL Server to allow SQL Server Authentication, i.e. authentication using username and password.

You can't authenticate by domain username/password 'like' server authentication, i.e. specify domain username/password directly.

I can be wrong of course, but I'm sure that this isn't a problem of C# or .NET. How can you login on SQL Server in your Perl or Java application??

Answer 2

Is useless to specify user name and password in connection string because those imply SQL Authentication, and you already specified that SQL Server only accepts Windows authentication.

If the server doesn't allow SQL Authentication then the only possibility to connect is to use Windows authentication, ie. IntegratedSecurity=true. Which means that your client will authenticate as whatever credential is running the process (or is being currently impersonated).

In order for Windows authentication to work, you have to choose one of the following:

• Join the non-domain joined machine into a domain (it can be it's own domain!) that trusts the server domain, then run the client process as a domain\user credential.
• Use NTLM mirrored accounts: a pair of local users on the client and the server with identical name and passwords.
• Grant as ANONYMOUS access to the SQL Server.

If you cannot make the client host trust the server domain, nor can you add NTLM mirrored accounts, and the SQL Server admin is sane enough not to enable ANONYMOUS then you won't be able to connect.

Answer 3

Hi - I'll give you the Java answer which I'm more familiar with: I use the jTDS JDBC driver with the four parameters mentioned above. The Perl application I know less about, but is running on a Linux box, and is able to connect with the same parameters. I cannot change the SQL Server to support SQL Authentication.

To answer Remus' suggestions, I cannot do any of those three things he suggests and yet Java and Perl applications are able to connect. Any other ideas?

Thanks, Martin

Answer 4

Is it an option to prompt for credentials?

Answer 5

Here is the sample code that I use to connect from a non-domain machine using the jTDS JDBC driver:

Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance(); String url = "jdbc:jtds:sqlserver://server/database;domain=domain"; conn = DriverManager.getConnection(url, "user", "password");

Answer 6

bai nay do lam .k can dau .can k co pass kia!!!!

Answer 7

As you correctly say, JDBC or Perl on a Linux machine can both connect to an SQL Server using Windows authentication and credentials which differ from the currently logged on user. The same is true for Windows CE devices, by the way.

I think that this is that this is not an issue of C# but of the SQL Server OLE DB driver. I guess the methods mentioned above "pretend to be a Windows machine using some specific credentials" on the network level; a feature, which the SQL Server OLE DB driver lacks. Thus, my suggestion would be to look for an alternative (maybe commercial?) OLE DB driver that can access SQL Server databases. I'm not sure if such a thing exists, though.