SQL "login failed for user [username]" error for windows service, but SSMS login works

Question

I'm writing a windows service that needs to connect to a SQL server to retrieve some information. However, the server doesn't allow me to connect: login failed for user [domain/username]. The strange part is that everything works totally fine from inside SSMS. I can log in, query the database, and everything works out perfectly. It's only through the windows service that it's throwing that error.

For reference, here's the connection string I'm using:

Data Source=sqlserveraddresshere;Initial Catalog=databasenamehere;User ID=domain\username;Password=password`

I doubt that this is related, but when I try to use Integrated Security it's getting my computer name instead of my own user name. I'm 99% sure this is because our anti-virus and monitoring software is spoofing an administrator user on top of my windows session to do its job. In any case, just entering the correct username and password should be working regardless, right?

I took a look at some other questions involving the same error, but they were all unrelated. From what I can tell everyone who can get in through SSMS can access their SQL server through code, too. This is the most similar question I could find, although it didn't help much: Can't connect to SQL Server: "Login failed for user "."

Also, before you ask, I HAVE ALREADY CHECKED FOR TYPOS. Actually, I've even tried to use other user's accounts to make sure there wasn't something funky going on with my permissions. If you need me to post any code let me know. Thanks for your help!

UPDATE:

I changed the account for the service and switched back to IntegratedSecurity = true. It seems like something has changed, because now I'm receiving a slightly different error:

Cannot open database "mydatabase" requested by the login. The login failed. >Login failed for user 'MYDOMAIN\myUserName'.

Solution 1

It's working! Finally!

First, using Kevin's suggestion (comment underneath the original question), I checked through the SQL Server Logs (in Management Studio, expand server, expand Management, expand SQL Server Logs). This is how I found more lengthy error reporting that helped me diagnose the issue.

Turns out the server didn't allow anything other than Windows Authentication, so that's why my attempts at entering the UN/PW manually failed.

However, I still had the issue where the server was passing my machine name as the user instead of my username. This was fixed with Duncan's suggestion of changing the user that the service ran under to my name (right click in Services->Properties->Log On Tab).

Thanks to everyone for your help, much appreciated.

Solution 2

You need to check the user account that the service is running under. By the sounds of things it is running as Local System which will try and pass the machine name through as the login.

Try changing this to a least privileged domain user (or for testing, you could use your own account) and then granting that user a login to the SQL Server. That should do the trick.

Comments

[VVander]

Sorry if I didn't make this clear in the original question. I changed the values in the connection string before posting it. In my program I'm actually using the username and password that should have access to the server.

[VVander]

Ok I changed the account for the service and switched back to IntegratedSecurity = true. It seems like something has changed, because now I'm receiving a slightly different error: Cannot open database "mydatabase" requested by the login. The login failed. Login failed for user 'MYDOMAIN\myUserName'. I'll add this to the original question, too.

[EastOfJupiter]

When I get home tonight, I'll post my connection string that I use with my application. It's set to use SQL Server authentication, but I presume it should serve as a starting model for any type of authentication.

[VVander]

Thanks, any help is appreciated. Although I have been using connectionstring.com's examples as well as using MS's ConnectionStringBuilder class in .NET, if your string is different it could help.

[EastOfJupiter]

In my experience with connectionstring.com (which is a wonderful resource) they are extremely generic. I should have something up here around 6:00 - 6:30 Central Time. BTW, are you planning on using Windows authentication or SQL Server authentication?

[VVander]

I'm now using Windows Authentication since I found the server only accepts that. I finally figured everything out, so I don't need your help after all, but thank you anyway. If you're curious, I posted the solution as an answer.

[Duncan Howe]

You probably need to create a database user for the login. Right click on the login in the Object Explorer in SSMS and check the User Mappings in the left hand pane. Alternatively you can run CREATE USER [blah] FOR LOGIN [login] against the database

[VVander]

In the end it turns out I entered the database name wrong and that's why your suggestion didn't work at first and gave me that error. This might help others though.

[bluwater2001]

To add to VVander's answer, on the Log On tab, select "This Account" radio button and click browse. Type in "your name" and the system will do the AD search to authenticate the username. then type in your system password and apply. You should get a message something like "system accepts the username/password for this service.." . that's it. It will work.