Windows Service SQL Server error - "Login failed for user"

Question

I have a Windows Service written in C# being run on a Windows Server 2012 R2 machine which connects to a remote SQL Server 2012 instance also on a Windows Server 2012 R2 Server.

The SQL Connection String is defined in the App Config as follows:

<connectionStrings>
    <add name="destinationConnectionString" providerName="System.Data.SqlClient"
    connectionString="Server=10.42.42.10;Database=mydb;User ID=myuser;Password=mypass" />
</connectionStrings>

When running the service, the output error log shows:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'myuser'.

Error Number:18456, State:1, Class:14

So I check the SQL Server Logs to find out more information about the error and find this

Login failed for user myuser Reason: Password did not match for the login provided"

Source: Logon Message: Error: 18456, Severity: 14, State: 8.

The problem is I know these credentials are correct. I've checked and double checked.

I am able to prove the credentials work by opening SQL Server Management Studio on the source machine, and connecting to the remote SQL Server using the exact same Server, User ID and Password with "SQL Server Authentication" authentication mode.

In fact I have 2 different users that I'm able to connect with using SSMS, but that fail using the Windows service. If I am able to connect to the remote machine using mixed mode auth directly in SSMS. Why not in my Windows service?

I'm at a loss here, could anyone suggest what the issue might be?

Edit: I can even successfully connect to the remote machine using the following command:

sqlcmd -S 10.42.42.10 -U myuser -P mypass -d mydb

Answer 1

The proper syntax for specifying the initial database is:

Server=10.42.42.10;Initial Catalog=mydb;User ID=myuser;Password=mypass

My best guess is that it is ignoring the "Database" in your string and possibly trying to connect to the default database setting for the login (possibly master maybe?) and doesn't have at least public role to it. Although I've found out that "Database" is a valid alternative.

It's possible there is a character in your password that is tripping it up. Semicolons and equal signs will be particularly problematic. Try putting double quotes around your User ID and Password like:

Server=10.42.42.10;Initial Catalog=mydb;User ID="myuser";Password="mypass"

If there's a quotation mark in your password you will have to replace it with two of them for each occurrence.

Once you get your connection string built, then you have to ensure that it will parse properly in XML. Which means escaping control characters and the other special characters such as:

< with &lt;
> with &gt;
& with &amp;
' with &apos;
" with &quot;

If you open up your app config file in a browser it should show you if it looks proper.

I recommend adding the contents of your entire connection string to our output log of your service. That way you can see what it looks like. At the point before you attempt to open the connection, get it with:

string connStr = ConfigurationSettings.AppSettings("myConnectionString");

Then output the connStr value to the log. It will show you what values are being used at least.