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.
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?
(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 (How to build RUNAS /NETONLY functionality into a (C#/.NET/WinForms) program?) 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.
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.
Did you try connecting with machineName\UserName ? I'd also imagine that such a user should be created in the SQL database well - correct?
