December 9, 2009 at 8:43 am
Hi All,
I have windows service that accesses database. I need that service running before user logged in. I set the service running under LocalSystem account which corresponds to this user name NT AUTHORITY\SYSTEM. I added this user under Security/Logins and mapped to database I need to access. I also set default database for that user as my database. I gave permission to read database for that user. I also run these procedures EXEC sp_grantlogin [NT AUTHORITY\SYSTEM]; GO EXEC sp_grantdbaccess [NT AUTHORITY\SYSTEM]; Go
My connection string is Integrated Security=SSPI;Database='**database**';Addr='**SQLSERVERNAME**';
After failing with all these steps I tried to switch my service running under mycomputer\administrator and sets the same permissions that I did for NT AUTHORITY\SYSTEM. Still no luck. Login failed. Of course I do not get this error when I log in and start service from Services console. I am running Windows 2003 server and SQL server 2005 Anyone could help me to overcome this issue?
I tried running the same service on XP machine with SQL Server 2005 and no login failed error occurred.
Thanks a lot in advance, Roman
December 9, 2009 at 11:29 am
What DEFAULT database did you assign to them and did you grant them access to that also?
Oops, never mind...:blush:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 9, 2009 at 11:37 am
Things to check:
1) use Task Manager "Processes" tab w/ Username & "Show processes from all Users" to see if it is *really* running under that username.
2) Look in the SQL Server Logs to see if more explanation is given for the Logon failure.
3) Use SQL Profiler to try to get more detail on exactly what is happening in SQL Server when it tries to Logon (compare to a sucessful one).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 10, 2009 at 7:27 am
1) Checked, the process running under SYSTEM user
2) SQL server error log says error State=16.
After googling found this
State=16 means that the incoming user does not have permissions to log into the target database. So for example say you create a user FOO and set FOO's default database to master, but FOO does not have permissions to log into master.
This can also happen if for example the default database for user FOO is not online (for example the database is marked suspect).
3) Not sure how to start tracing with SQL Profiler when computer boots and my winservice starts.
Thanks for any advises
Roman
December 10, 2009 at 8:27 am
Roman-334857 (12/10/2009)
2) SQL server error log says error State=16.After googling found this
State=16 means that the incoming user does not have permissions to log into the target database. So for example say you create a user FOO and set FOO's default database to master, but FOO does not have permissions to log into master.
Just to be sure, check that the default database assigned is correct, and that it really does have connect permissions to it.
This can also happen if for example the default database for user FOO is not online (for example the database is marked suspect).
3) Not sure how to start tracing with SQL Profiler when computer boots and my winservice starts.
This raises two other issues:
4) You can start and stop the service yourself in order to test it and to use profiling.
5) Since I assumed that you were already either doing (4) or that it connected on-demand only, I did not mention the possibility of startup timing issues. It may be that your winservice is starting before the SQL Server has finished bringing all of the databases online. You should first make sure that you winservice had a service dependency on the SQL Server Service defined. The secondly you can use the technique in (4) to see if it connects successfully *after* startup. If not, then you'll need to look at the profiling traces to see where it is going wrong.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 11, 2009 at 1:41 pm
Thanks for response.
Actually I had added SQLServer dependency to my service from the beginning. It allowed me to get rid off different error "SQLServer was not accessible" or something like that. My initial post was related to "Login failed error". I did one test and delayed querying database from service for few minutes. I think during that time my database was successfully came to online mode and finally "Login failed error" never come up. Looks like it is not enough to wait until SQL server started to query database from windows service. You need also wait until database is connected. Weird enough.
I do not see any way how to check if database online instead of waiting to access database for few minutes. Any suggestions?
Thanks
Roman
December 11, 2009 at 4:45 pm
You mean from a program? The SOP way is to just try to connect to it. If you're changing the code tha I would recommend setting your service up with a retry counter, or something like that. Otherwise, may you could raise the connect timeout?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply