Connecting to an mssql server from a remote machine gives Login failed for user ''

Question

I get this error when I try to connect to a remote SQL server using this connection string.

Error:

ODBC error: 28000118452[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection.

Connection string:

"DRIVER={SQL Server};SERVER=testserver,1433;Trusted_Connection={Yes};"

Note this same connection string works fine if I specify the local computer.

My question: Why does the remote SQL server think my username is blank ''?

I did a valid login using the WNetAddConnection Win32 API to the remote machine first.

Edit: I get the same error when connecting from Management studio. But I thought my program would have a higher chance of working since I established a connection to the remote machine first.

Edit2: Note I really need a solution that uses Windows authentication. I already have it working with SQL authentication.

Answer 1

You might need SQL authentication by providing user and password in the connection string.

EDIT. I was curious to find out and I just found a good documentation of How to: Access SQL Server Using Windows Integrated Security

Answer 2

I think that error might be because the UserId section isn't filled in (which is correct for Windows Auth).

Try a connection string like:

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Another alternative which is supposed to be identical:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Answer 3

Thoughts:

1.) are both machines in the same domain? I am thinking probably so because of your references to making a connection with WNet*...

If not, Integrated Security will give you problems.

Edit: I see that both machines are not in the same domain. To use Integrated Security, the domain that the SQL Server is in must trust the domain your local machine is in.

Another Edit: Corroboration for the above statement.

From link: This error message can appear if the user logging in is a domain account from a different, untrusted domain from the SQL Server’s domain. The next step for this is either to move the client machine into the same domain as the SQL Server and set it up to use a domain account, or to set up mutual trust between the domains. Setting up mutual trust is a complicated procedure and should be done with a great deal of care and due security considerations.

2.) Check to be sure the SPNs are set properly on the server.

Look HERE for more guidance, but basically you do:

setspn -L servername

one of the SPNs needs to look like:

MSSQLSvc/servername:1433

3.) One more Edit: If neither machine is in a domain, all you need to do is have a local userid/password that matches on both machines.

For example, a user named brian on both machines, and both users passwords are 'letmein'.