SQL Server 2005 server login worked, then did not

Question

Post reply

SQL Server 2005 server login worked, then did not

crossmj

SSC Journeyman

Points: 82
More actions
January 24, 2010 at 11:17 am

#209368

I recently installed Sql Server 2005 Standard Edition on a newly installed Windows Server 2003 server which is joined to an Active Directory domain.

The Windows server normally logs in as <DomainName>\Adminstrator. I configured the Sql server to log in using my domain account which is a member of the Domain Admins group.

This configuration allowed the various Sql Server services to start automatically when the Windows server restarted. I restarted the Windows server several times in the process of installing Microsoft updates and each time the Sql Server services started automatically.

Several days later, after I had created a database on the Sql Server, I installed a Microsoft update for IE8 which required a restart. When the Windows server restarted, the Sql services did not start due to a login failure.

After resetting my domain account in Sql Configurartion Manager failed to resolve the problem, I set the Sql Server to login using the Network Service account.

Does anyone have an educated guess as to why my domain account lost security clearance and what I could/should do about it?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Gail Shaw

SSC Guru

Points: 1004494

January 24, 2010 at 11:40 am

#1107941

Password change?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 2

Its not a good idea to run SQL services under an account used for other purposes, such as a personal account. A domain admin account is also a security risk. Set up a domain user account with a (secure) password set to never expire for the explicit purpose of running SQL.

As Gail said a password change to your id would prevent SQL starting the next time it was bounced. Any number of changes made to your acount in AD could have had affected its ability to run SQL.

---------------------------------------------------------------------

crossmj SSC Journeyman Points: 82 More actions · Answer 3

Thanks for your reply. I will take your advice and create an AD user account for the Sql Server instance. I am, however, concerned that such an account might prohibit me from creating objects and running scripts via a remote desktop connection and/or a linked server created in a different instance (Sql Server 2005 Developers Edition).

Any feedback on that issue?

george sibbald SSC Guru Points: 104210 More actions · Answer 4

This account is only to run the SQL services.

You then add your personal ID as a login in SQL and grant it the permissions you wish it to have. If you have rights to logon to the server you can RDP to it (this has nothing to do with SQL), once on the server you can connect to SQL.

you will also be connect to the SQL instance using SQL client tools such as SSMS remotely from anywhere within the domain, or any other trusted domain.

---------------------------------------------------------------------

crossmj SSC Journeyman Points: 82 More actions · Answer 5

My service account logon problem is apparently resolved.

As you suggested, I created a domain user account. However, the new account still lacked sufficient logon rights. After much researching and head scratching and trial and error, I now have the following setup.

The Windows server is now a member of Builtin Administrators.

The new domain user account is now a member of Builtin Adminstrators and Domain Users

The new new domain user account has User Rights to logon as service in both Domain Controller Security Policy and Domain Security Policy.

I am not sure which one or combination of these changes resolved the problem, but if you think some of these changes are inadvisable and/or dangerous please let me know.

Thanks for your help.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

george sibbald

SSC Guru

Points: 104210

January 26, 2010 at 12:12 pm

#1108873

builtin\administrators is more rights than it strictly needs.

did you use SQL configuration manager to assign the new user as the service account? That would automatically assign it all rights it requires to run SQL.

---------------------------------------------------------------------

crossmj SSC Journeyman Points: 82 More actions · Answer 7

I did use Sql Config Mgr to change the account from Network Service to the new domain account. However, the services failed to start due to a login failure until I manually assigned the logon as a service rights on a domain controller. I do not know why rights were not assigned automatically.

If I removed the new sql services account from the Builtin Admins group, would that compromise the account's credibility with Sql Server?

george sibbald SSC Guru Points: 104210 More actions · Answer 8

crossmj (1/26/2010)

If I removed the new sql services account from the Builtin Admins group, would that compromise the account's credibility with Sql Server?

No. You should find the account is explicitly defined in SQL as a login with sysadmin rights

---------------------------------------------------------------------