Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a pretty ridiculous problem.

So I've installed SQL Server 2005 on a Window Server 2008 machine. I was working my way through the service packs, provisioning two users to have full rights to the SQL database.

I installed SP3, rebooted, and now I cannot start any of the SQL services. When I try to log into SQL Management Studio, it tells me that remote connections aren't allowed, (they are, im 100% sure) and gives me error message 2.

I haven't the slightest clue on why this would be happening, so any help even in understanding the nature of this problem would be helpful.

Log Name: System
Source: Service
Control Manager Date: 2/9/2010 9:29:07 AM
Event ID: 7041 Task
Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: forefronttest.mca.xxxx.xxxx Description: The MSSQLSERVER service was unable to log on as MCA\forefront with the currently configured password due to the following error: Logon failure: the user has not been granted the requested logon type at this computer.
Service: MSSQLSERVER Domain and account: MCA\forefront
This service account does not have the required user right "Log on as a service."

User Action
Assign "Log on as a service" to the service account on this computer. You can use Local Security Settings (Secpol.msc) to do this. If this computer is a node in a cluster, check that this user right is assigned to the Cluster service account on all nodes in the cluster.

If you have already assigned this user right to the service account, and the user right appears to be removed, check with your domain administrator to find out if a Group Policy object associated with this node might be removing the right.

Event Xml:

 <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
     <Provider Name="Service Control Manager" Guid="{555908D1-A6D7-4695-8E1E-26931D2012F4}" EventSourceName="Service Control Manager" />
        <EventID Qualifiers="49152">7041</EventID>
        <Version>0</Version>
        <Level>2</Level>
        <Task>0</Task>
        <Opcode>0</Opcode>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2010-02-09T17:29:07.000Z" />
        <EventRecordID>1938</EventRecordID>
        <Correlation />
        <Execution ProcessID="0" ThreadID="0" />
        <Channel>System</Channel>
        <Computer>forefronttest.mca.xxxx.xxxx</Computer>
        <Security />
     </System>
     <EventData>
        <Data Name="param1">MSSQLSERVER</Data>
        <Data Name="param2">MCA\forefront</Data>
     </EventData>
 </Event>
share|improve this question
    
Any errors in the event log when the services try to start? –  MattB Feb 9 '10 at 17:47

1 Answer 1

net localgroup SQLServerMSSQLUser$forefronttest$MSSQLSERVER /add MCA\forefront

This will add the MCA\forefront user to the local group created during SQL setup that has all the required priviledges to run SQL Server service. See Setting Up Windows Service Accounts.

share|improve this answer
    
both the local administrator forefronttest\administrator and a domain admin MCA\forefront were provisioned by the Microsoft SQL user provisioning tool. before i restarted the machine and got locked out, both accounts were listed in the security folder. –  kyoung Feb 9 '10 at 19:17
    
The provisioning tool adds the accounts you mentioned as members of the sysadmin server role in the instance. That is for SQL own internal security, in order to allow the provisioned account full admin access to the instance, once is up and running. –  Remus Rusanu Feb 9 '10 at 21:03
    
What I'm recommending is pure Windows security, not related to SQL internal roles and groups. During setup a local group was created on FOREFRONTTEST machine. This group is called SQLServerMSSQLUser$forefronttest$MSSQLSERVER. All the necessary priviledges, registry keys and file access rights were granted to thsi group. Then the service account configured during setup was added to this group, thus inheriting all the necessary priviledges to start the service. –  Remus Rusanu Feb 9 '10 at 21:05
    
Later somehow the service account was changed and the result is that the new service account (MAC\forefront) does not have the encessray priviledges. It has to be added to the group to get all the necessary priviledges. In theory it can be granted all the necessary priviledges and all registry key and file access rights needed, but that is cumbersome and error prone. Much easier is to just add him to the group that is specifically configured for this. –  Remus Rusanu Feb 9 '10 at 21:06
    
System Error 1376: The specified local group does not exist –  kyoung Feb 10 '10 at 16:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.