Access to SQL Server 2005 from a non-domain machine using Windows authentication

Question

Hi, 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 thought that it would be a simple matter of doing something like this:

string connectionString = "Data Source=server;Initial Catalog=database;User Id=domain\user;Password=password"; SqlConnection connection = new SqlConnection(connectionString); connection.Open();

However, this fails: the client-side error is: System.Data.SqlClient.SqlException: Login failed for user 'domain\user' and the server-side error is: Error 18456, Severity 14, State 5

I have tried various things including setting integrated security to true and false, and \ instead of \ in the User Id, but without success.

In general, I know that it possible to connect to the SQL Server 2005 instance from a non-domain machine (for example, I am working with a Linux-based application which happily does this), but I don't seem to be able to work out how to do it from a Windows machine.

Help would be appreciated! Thanks, Martin

Answer 1

Did you try connecting with machineName\UserName ? I'd also imagine that such a user should be created in the SQL database well - correct?

Answer 2

With Management Studio when connecting to a server on another domain via Windows Authentication you need to use the "runas" Facility when starting the application.

 runas /user:OTHERDOMAIN\OTHERUSERNAME  /netonly 
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

Maybe you could try an analogous thing when starting your application?

Answer 3

It is possible to create a local user on each machine with the same user name and password Once these users have been created give the user on the SQL server machine access to SQL server, on your other machine your application must be running as the newly created user, if is a web app simply change application pool settings.

Answer 4

(Per Martin Smith's answer) I use RUNAS /NETONLY all the time to run SSMS and the BI dev studio and Visual Studio IDE and other 3rd party applications against a server on a domain we do not authenticate against. I'm not sure why you can't do this - you must somehow have an account on that domain which works with SQL Server if you are using integrated security.

In addition, I modified one of my main C# programs to use CreateProcessWithLogonW with LOGON_NETCREDENTIALS_ONLY (http://stackoverflow.com/questions/757857/how-to-build-runas-netonly-functionality-into-a-c-net-winforms-program/758805#758805) so that it prompts for username and password and then re-launches itself.

Using the same API, I also made a version of RUNAS /NETONLY which will accept a password on the command-line, since RUNAS will NOT allow a password on the command-line. Obviously, this is an inherent security risk, and I don't use it frequently.

As a matter of completeness, there is also this great shell extension: http://technet.microsoft.com/en-us/sysinternals/cc300361.aspx - the regular Run As Shell extension doesn't have the ability to do the equivalent of /NETONLY.

Answer 5

Hi - runas is not going to be acceptable because of the ways in which we'll be using the C# application. The problem I am having with all of the proposed solutions is that they pre-suppose some need to either change the identity on the client side or to synchronize in some way user accounts on the client and server side.

However, in general I know that that it is possible to connect to the SQL Server instance without doing either of these things because I can use the application running on the Linux box I mentioned in my first post. In this application, I only pass in the four parameters (server, database, domain\user, and password) and the application connects quite happily. How is it that a non-Windows box running Perl can do what a Windows box running C# cannot?

Thanks for any help. Martin