October 18, 2010 at 8:28 am
I'm running SQL Server 2008 R2 in a Windows 2008 Enterprise Hyper-V virtual. I've created a service account to run the SQL service. I've set up the SPN for service account correctly, i.e.
MSSQLsvc/<machine name>:1433 and with the FQDN
The service account has been granted "log on as service" and some other local policy privilages. The Force Protocol Encryption is set to NO. The VIA protocal is disabled.
When I set the SQL service to run as this domain service account with the SQL Configuration manager the service won't start and throws a bunch of errors which I'll post below. If I set the SQL service to run as Network Service, it works. If I use the Built-in "Administrators" for the domain it works.
From Application Event Log...
Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.
Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
Server name is 'SQ01'. This is an informational message only. No user action is required.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
From System Event Log..
The LoadUserProfile call failed with the following error: Access is denied.
The SQL Server (MSSQLSERVER) service entered the stopped state.
The SQL Server (MSSQLSERVER) service terminated with service-specific error 2148081668 (0x80092004).
October 18, 2010 at 1:43 pm
anything extra in the SQL error log? Does it even produce an error log?
does it work if you give the account local admin?
---------------------------------------------------------------------
October 18, 2010 at 2:03 pm
george sibbald (10/18/2010)
anything extra in the SQL error log? Does it even produce an error log?does it work if you give the account local admin?
Local admin privilages doesn't work. Honestly, I think it's how I set up and configured the service account. In the Win2003 world, I would simply create a new user account, grant the service account certain privilages in the machines local policy, in the DB, set the account SPN and be done with it.
I was reading about service accounts in 2008 with resepct to managing them. It's done a little different with the new OS. Here's a good article on it
http://www.windowsecurity.com/articles/Controlling-Service-Security-Windows-Server-2008.html
Going to try out the items listed in the article and see where I get.
October 18, 2010 at 3:04 pm
Ok, confused now. change of username or schizophrenic? 🙂
good old windows 2008, check out UACL.
---------------------------------------------------------------------
October 18, 2010 at 3:14 pm
turfnsurf4me (10/18/2010)
MSSQLsvc/<machine name>:1433 and with the FQDN
This is incorrect, when running SQL Server under the context of a user account the SPN is bound to that user and not the server name. Like so
SETSPN -A MSSQLSvc/computer.domain.com:TCPPort domain\useracct
So for my test domain UKTRADING and SQL Server MYSERVER with SQL Server instance using port 1608 I would bind to user account SQL-Service-srv like so;
SETSPN -A MSSQLSvc/myserver.UKTRADING.co.uk:1608 UKTrading\SQL-Service-srv
Delete the SPN against the computer name and re register as above!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2010 at 3:01 am
Perry Whittle (10/18/2010)
turfnsurf4me (10/18/2010)
MSSQLsvc/<machine name>:1433 and with the FQDNThis is incorrect, when running SQL Server under the context of a user account the SPN is bound to that user and not the server name. Like so.....
No.......it's not. What I neglected to articulate is that I use the ADSI Edit snap-in and edit the service principal name property ( of the service account) directly while on the domain controller. I don't use the command line interface to create or modify the property. You see what I'm saying? Trust me...the SPN is correct. I've been doing this for years.
When I'm done doing what I just said, the SPN is...
MSSQLsvc/sq01:1433
MSSQLsvc/sq01.virt.local:1433
And even if I did have the SPN hosed, it still wouldn't explain why the process doesn't start with some ID's and does with others. In other words, the correctness or incorrectness of the SPN doesn't determine whether or not the process will start or won't start. At least it never has in the years I've been using them. Sure, you'll get errors being thrown in the Event logs, and communications between processes won't work. But it won't effect whether or not the process starts.
October 19, 2010 at 3:28 am
george sibbald (10/18/2010)
Ok, confused now. change of username or schizophrenic? 🙂
I have two screen names.... :w00t:..... simply because one day I forgot I had the other one. So I "re-registered". And while at work yesterday, I was "the other guy". Uhhhh....I don't come here very often. Bottom line, both usernames ( screenames) are for the same people.. me.
And I've already disabled the UACL on the machine. So that's not it.
October 19, 2010 at 4:16 am
turfnsurf4me (10/19/2010)
What I neglected to articulate is that I use the ADSI Edit snap-in and edit the service principal name property ( of the service account) directly while on the domain controller. I don't use the command line interface to create or modify the property. You see what I'm saying? Trust me...the SPN is correct. I've been doing this for years.When I'm done doing what I just said, the SPN is...
MSSQLsvc/sq01:1433
MSSQLsvc/sq01.virt.local:1433
I see, the SPN above is what you see via ADSIEDIT against the service account. I wrongly assumed you had listed against the computer account. You would be surprised how many people do not understand that the SPN should be bound to the service account and not the computer account when running the service under the context of a low privilege user account!
turfnsurf4me (10/19/2010)
And even if I did have the SPN hosed, it still wouldn't explain why the process doesn't start
I never said or implied that it did, my reference was to the seemingly incorrect SPN binding.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2010 at 4:27 am
I never said or implied that it did, my reference was to the seemingly incorrect SPN binding.
You're right and pardon me for coming off kind of snippy, it wasn't my intention, sorry. At any rate, I just fired up another clean 2008 virtual to install SQL server 2008. Figured I hosed something up in the original install. Everything was fine up until I get to the section where I specify a service account for the processes. The error I get is
*S-15-21-some long *** number-another ridiculous number-1108. No mapping between account name and the security ID's was done.
It has be something with my infrastructure, or the way I created the service account, which I can't imagine what because I created the blasted thing like I always have with gui tools etc. I'm lost...
October 19, 2010 at 4:42 am
Incidentally, rathing than poking around manually setting SPN's just allow the service account to do it automatically.
In ADSIEDIT find you service and account and select properties then go to the permissions tab. Scroll down and find SELF "read\write personal information" and click edit. Scroll down and check the following
Allow read ServicePrincipalName
Allow write ServicePrincipalName
The service account will now automatically register and maintain it's SPN's.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2010 at 4:53 am
Perry Whittle (10/19/2010)
Incidentally, rathing than poking around manually setting SPN's just allow...
Good tip! Thanks I'll remember that. I'm googling that error message about not being able to map the account name to security ID's. I figure once I get solved, I'm good to go.
It's always something with "Microbloat" and Bill gates. Hell, I spend more time configuring/applying "fixes" { eyes rolling back} & updates and the like with these blasted Windows OS's than I do programming (which I normally do). It gets old..
So now the question is ( for me at any rate) is this...
A) A SQL Server 2008 R2 issue?
B) A Windows 2008 (Enterprise SP2) network infrastructure/configuration issue?
C) A little bit of both?
October 19, 2010 at 7:07 pm
Problem solved!... Turns out it was the way I sysprep'ed ( or lack there of ) the SQL machine. Have to check the "Generalize’. check box. It's unchecked by default. Geez...there was a waste of a day or two.
SQL Server is installing right now all happy..... Thanks for your help.
October 20, 2010 at 2:54 am
thanks for the feedback. As someone who does not build the servers this is useful to know. And of course the info could make someones day if they have the same error.
---------------------------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply