2

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>
1
  • Any errors in the event log when the services try to start?
    – MattB
    Feb 9, 2010 at 17:47

1 Answer 1

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.

6
  • 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, 2010 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. Feb 9, 2010 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. Feb 9, 2010 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. Feb 9, 2010 at 21:06
  • System Error 1376: The specified local group does not exist
    – kyoung
    Feb 10, 2010 at 16:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .