Login failed for user 'xyz' when a windows service tries to connect to local database

Question

Hi

I have created a windows service that calls another class (which sends emails daily after checking the database for subscribers). This service runs under Local System account and set to automatic. When i change the logon account to Network service, i get the same errors.

My first problem is : when i bootup(shut down and then start again) my system, the service does start automatically but gives me an exception - 'login failed for the user'. Although i have this user set up in the database with db_owner rights and i can login to sql server with this login fine.

2. when i restart my system, sql server isn't started so gives me a different exception - cannot connect to database.

3. when i manually start the service with sql server running (either automatically or manually), i get the required emails.

4. when i try a different box to see if the service works there, the service starts and stops automatically..even though, the database is configured to allow remote connections and i also changed the connection string ="Server=<machinename>;User ID=<userid>;Password=<pwd>;Initial Catalog=<dbname>".

I am new to windows services and sql server so any help is appreciated.

Thanks

Answer 1

Hello Vidya PLC,

Your problem is a well-known one.But the solution is depending of the full message as it is missing the 2nd part of the message.

There is an excellent blog managed by the Microsoft team who is managing the protocols :http://blogs.msdn.com/b/sql_protocols/ 

Nearly every problem of connectivity can be solved with the articles from this blog.

For example : http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

More generally http://blogs.msdn.com/b/sql_protocols/archive/2005/09/28/474698.aspx

More vicious : http://blogs.msdn.com/b/sql_protocols/archive/2009/09/21/connection-from-a-windows-service-could-be-blocked-by-firewall-even-if-firewall-is-disabled.aspx

http://blogs.msdn.com/b/sql_protocols/archive/2008/05/03/understanding-the-error-message-login-failed-for-user-the-user-is-not-associated-with-a-trusted-sql-server-connection.aspx

This blog is the REFERENCE for any connectivity problem and i would suggest you to include in your favorites . And 90% of the solved threads have their solutions coming from this blog.So i would suggest you to study fully this blog. All i regreat is that this blog seems an too often  "unknown" one, even Bing and Google seem to avoid it.

Of course, don't hesitate to post again for more help or explanations.

Have a nice day 

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

So now i am connected to the wired network that starts my windows service as well as sql server 2008 on system boot up. But i get the same exception - cannot open database xyz requested by the login. The Login failed. The login failed for the user 'abc'.

Your first link lists out various error messages. But i do not get any state information in my error. I don't get any of the error messages from your second list either. I had already created a rule to allow sql server connections on my firewall. and your fourth link explains a different error message. My error message is  mentioned above.

Any other suggestions!

Thanks.

Answer 3

Hello,

I am thinking that the login you are using to connect has no access to the database ( an offline database would be giving a different message )

Please, could you open SQL Server Management Studio ( SSMS or SSMSE if SQL Server Express ) with a login who has access to your database ( db_owner could be useful ) ?

Double-click on the name of your SQL Server instance to develop the nodes

double-click on the Security node to develop it

double-click  on the Connections node to develop it

right-click on the name of the login you are using to try to log and in the conceptual menu select the last item Properties

You will arrive to a new form , in the left part of the form click on the 3rd item Mapping of the user or something like ( i have only a french SQL Server Developer 2008 and my translations may be approximative ).

Please, select your database xyz and could you give us some informations ?

- in the upper part is the checkbox checked for the xyz database ? ( it means that the xyz database has some mapping for the login )

- in the lower part ( corresponding to the access permissions to the database for the login ) , could you tell us which checkboxes are checked ?

the minimum permissions are db_datareader + db_datawriter ( like in the screenshot )

I hope you will excuse me for my french SSMS.

Don't hesitate to post again for more help or explanations.

We are waiting for your feedback to try to help you more efficiently.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 4

Thanks Papy for the quick reply. But the user is correctly mapped to the database and the checked checkboxes are : public and db_owner.

I tried recreating the service under User account and the service runs correctly. I am supposed to run it under Network service account and that gives me the same error - cannot open database xyz requested by the login. The Login failed. The login failed for the user 'abc'.

Thanks Papy for bearing with me.

Answer 5

Hello,

What is the exact name of the user for your Windows service ? NT AUTHORITY\LOCAL SERVICE ?

Do you these links ?

http://msdn.microsoft.com/en-us/library/ms366351(SQL.100).aspx

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/720df337-7170-4126-834b-e1d0de738e11    ( see the last post of Jens SuessMeyer )

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/da64b280-6b90-4af2-a677-904e696a50f9

http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/16f906e6-8b31-4a42-9700-e7df64183920

http://blogs.msdn.com/b/mscrmfreak/archive/2007/03/27/login-failed-for-user-nt-authority-network-service.aspx   ( interesting if your windows service is starting before the SQL Server service but i hate to modify the registry )

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx 

if your SQL Server is at least a 2008 or higher one :

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/06/how-to-troubleshoot-connectivity-failure-error-with-sql-server.aspx

Don't hesiate to post again for more help or explanations

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.