C#/SQL Server: Login failed when connecting from remote machine

Question

I've got an SQL Server 2008 Express set up on one machine and am trying to connect with my C# app to the server from another machine (both Win 7). The server machine has a user account for me in the system. The SQL Server is configured to use Windows Authentication. Firewall on the server machine has inbound rules to accept ports 1433 for TCP and 1434 for UDP and also accept the server's exe.

The problem: I cannot connect to the server. Whatever connection string I use I get errors:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'SERVINGMACHINE\wally'.

or

System.Data.SqlClient.SqlException (0x80131904): Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

An example of my connection string:

SqlConnection myConnection = new SqlConnection("Integrated security=true;" +
  @"user id=SERVERMACHINE\wally;" +
  //"password=testpasswd;" +
  @"server=10.127.40.1,1433\SQLEXPRESS;" + 
  "Trusted_Connection=true;" +
  "database=nscm_db; " +
  "connection timeout=30");

I've commented out the password since it's allegedly not used for trusted connection.

When the app and the server are on the same machine the connection works fine.

What am I doing wrong?

Solution

If you are using trusted logins, you should not provide any login name. Connections will be made using whatever you are logged in with.

Are the two computers members of the same Active Directory domain? If not, you can try creating the same account on both machines, including matching passwords. I am not sure if that works under Windows 7, I have not tried it since the early days of Windows XP or maybe even Windows 2000.

The quickest way out for you is probably just to create SQL Server account and use that.

Comments

[Val]

I have already probably tried every possible combination of those values (including SSPI for integrated security). Nothing works from a remote machine but when running the app locally (on the same machine with the server) many different combinations work without problems.

[Val]

I'm now trying to use the untrusted (SQL server auth) connection

[Greg Askew]

That is what I would expect. Your credentials work on your machine because your machine trusts your identity. When you perform the activity against a remote computer, the remote computer does not trust the identity of the process that is initiating the connection. You should either use an account from a domain that both computers trust, or specify the credentials in the connection string (which in my experience is the most common scenario).

[Val]

How would I use an account from a domain that both computers trust if none of the machines is added to Active Directory? Should I somehow on the client machine change the domain to the one on the server machine?

[Greg Askew]

In that case, you may want to use a SQL logon and not a Windows logon.

[Val]

Yes, I am now struggling with the SQL Server Auth. And it seems I've managed to connect already but just can't perform some operation (like INSERT) in the database. I presume this should be configurable in User Roles somewhere

[Darin Strait]

When troubleshooting this sort of thing, the first command I try is 'SELECT @@SERVERNAME'. There are two sorts of roles, server and database. The server roles are aimed more at admin functions, the database roles are aimed more at restricting read/write of data for 'regular' users. You can create your own db roles and grant whatever security you like to them, then add users. Or you can just add users to the predefined roles.

[Val]

You know any simple tutorials for this that would let me create new users and assign basic roles quickly (ie in an automated way)?

[Darin Strait]

Nothing jumps to mind; I'd have to google something up. If you don't have a complicated security situation (some users need read and write on some tables but not others, and other users need read access on different tables, etc. etc.) (and since most web apps connect with just one user, you probably don't have a complicated situation), you can just add your user to the pre-existing db_datareader and db_datawriter roles. This will allow the user to read & write any table in the database. This can be done with SSMS or by using the sp_addrolemember system stored procedure.

[Val]

The app will be used by a small team of workers in a closed network so security doesn't have to be paranoidal. But setting the db_datareader and db_datawriter roles doesn't help. I had to go to each database and explicitly mark INSERT, SELECT, UPDATE and DELETE roles for each user account to make these operations work. Do you think I can create one account for all of the users to connect through? Will it work without any errors?

[Darin Strait]

The default roles should work. Note that these roles exist in each database and you need to add the user to those roles in each database. Just doing it in the master database won't get the desired result. You want to avoid granting permissions to each user because it's easy for them to get out of sync with each other and it's time consuming to do that for everyone. If you need to, create role, make the users a member of the role and then assign rights to that role.

[JohnLBevan]

If the connection's to a machine on a different domain, using AD credentials for that domain would that work? i.e. As implied: connectionstrings.com/sqlconnection/…