Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer
    
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 –  Val Cool Apr 21 '12 at 22:55
    
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. –  darin strait Apr 23 '12 at 11:26
    
You know any simple tutorials for this that would let me create new users and assign basic roles quickly (ie in an automated way)? –  Val Cool Apr 23 '12 at 16:54
    
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. –  darin strait Apr 24 '12 at 12:36
    
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? –  Val Cool Apr 24 '12 at 17:46

You're misunderstanding what "Integrated Security=true" means.

"True" means use the credentials of the current process, and do not specify a username or password.

SqlConnection.ConnectionString Property
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28v=vs.100%29.aspx

"...If User ID and Password are specified and Integrated Security is set to true, the User ID and Password will be ignored and Integrated Security will be used."

share|improve this answer
    
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 Cool Apr 21 '12 at 15:18
    
I'm now trying to use the untrusted (SQL server auth) connection –  Val Cool Apr 21 '12 at 15:19
    
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). –  Greg Askew Apr 21 '12 at 15:29
    
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? –  Val Cool Apr 21 '12 at 15:49
    
In that case, you may want to use a SQL logon and not a Windows logon. –  Greg Askew Apr 21 '12 at 15:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.