August 13, 2008 at 1:38 am
Hi guys,
this must be a common scenario, but I couldn't find clues on the net how to solve this issue.
For a standalone Dev environment I set up a (virtual) machine running Win Server 2003 and SQL Server 2005 Dev edition with all services except notification. The service accounts for all services have been set to 'network service'; authentication is set to Windows auth mode. After the installation SP2 was applied.
Next I sysprepped the machine, telling the answer file to choose a machine name by random.
After the first launch of the 'fresh' machine I ran the "sp_dropserver 'oldname'" / "sp_addserver 'newname', local" procedures. After reboot (didn't want to forget a service to be restarted) @@SERVERNAME returned the desired new machine name.
But: In my application log "Failure audits" (EventId 18456) keep popping up with the description Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [CLIENT: ] In the 'Data' part of the event properties it shows that the connection can't be made to the master db referenced with the new server name.
As I understand the issue (I'm a developer, not an admin) the security IDs of machine, services and accounts got changed by sysprep - so do what am I supposed to do about it? I tried changing the service accounts to 'Local System' and then back to 'Network service' without effect. The login failure keeps coming.
I'd really appreciate any clues in this issue. Hopefully I get around installing SQL and SP2 _after_ sysprep, for the sysprepped machine gets copied to about 15 consultants' machines...
Cheers,
Juergen
August 13, 2008 at 2:11 am
you should always rename the server before SQL is installed.
Un install SQL server, rename the server then re install
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 13, 2008 at 3:37 am
Hi Perry,
thanks for your suggestion - but I can't see why the renaming should be the problem. According to
the changed name can be made 'known' to SQL Server. I suspect the problem is connected with the logins.
I tried this: Sysprep on the machine - reboot - change the name manually back to the original name without sp_drop/addserver - reboot. This did not make the login failures go away...
Next experiment: Sysprepped the original machine and told it via the answer file to keep the actual machine name for a change. This worked, but my issue (the failed logins to SQL Server for network service) remained.
Sorry, I don't want to argue with you, just want to understand what's happening and if this is resolvable...
Cheers,
Juergen
August 14, 2008 at 1:35 am
Hi guys,
this blog entry helped me out a lot: I installed SQL Server on the guest OS without starting the services. After that SP2 followed.
Sysprepped the result and set the desired services to 'automatic' after giving the machine its final name. Had to sp_dropserver/sp_addserver and reboot afterwards - since then the ApplicationLog is silenced, no more login failures.
Have a nice one,
Juergen
August 15, 2008 at 8:00 am
Did you try going into SQL Server Configuration Manager and changing the services to 'Log On As' a local or domain account instead of Network Service? When you sysprep, it changes the machine passwords associated with Network Service, which would explain your event viewer errors since the passwords did actually change during the sysprep sequence.
August 15, 2008 at 8:25 am
Hi mstrong86,
no, I did nothing like that. When I installed SQL Server again on a backup vhd, I set the account for all SQL services to 'Local System' this time (these machines are not meant to go to production) and told the installer _not_ to start the services after installation.
Then I tried the sysprepping again. After first starting up the clone (when a new name is given to the machine) I ran a batch that set the startup type for the SQL Services to automatic, fired them up, replaced the old instance name of the server with the new machine name and finally restarted the services.
No hassles since then, even MOSS runs fine on that SQL server instance.
The sole purpose of this setup is to have a default machine for our consultants that they can quickly put on their notebooks, "one-click"-activate their configuration (with / without MOSS, PerformancePoint and the like) and go to their parts of the woods. And yes, some of them may connect to the same domain, therefore the sysprepping.
Cheers,
Juergen
August 19, 2008 at 2:43 am
Leaving the services as not started is not relevant. The SQL Server install starts and stops the SQL services a number of times. The SP2 install also starts and stops the SQL services while installing the fixes (some of the SP2 updates run T-SQL scripts to update system database contents).
Well done for getting the thing working, but it was not due to leaving the services stopped.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 19, 2008 at 3:51 am
Hi
I have been trying to resolve a similar problem for over a week and I do not believe it is possible without a lot of risky hard work. I don't know how many internet pages I have searched, but must be well into the hundreds!
The problem appears to be that SQL creates a number of local groups in Windows of the form
SQLServer2005MSSQLUser$Computer name$MSSQLSERVER
In my case, there are 10 of them depending on the number of services installed.
Some of these groups are then registered in SQL under security/logins and in the master database under security/users. In these two, the format is
Computer name\SQLServer2005MSSQLUser$Computer name$MSSQLSERVER
As I understand it, various built in service accounts are used by the various services for security and belong to these groups - I believe a new feature of SQL2005. The network service account is one of these.
When copying a VM, the computer name portion is not changed, so the local group in Windows and user/login in SQL has the incorrect name. Changing this in the Windows groups is relatively simple - I have a created script which will do it by searching the entire list of groups for old computer name and replacing with new computer name.
However, I have not found a way of doing the same thing in SQL. I cannot get past an error
"The name change cannot be performed because the SID of the new name does not match the old SID of the principal"
It may well be possible, but I have now resigned myself to the fact that it will be a fiddle, the built in account may well be orphaned and the whole process would require too much testing to ensure it will all work in a live environment.
I am currently working on copying the system without SQL preinstalled, and then using an unattended install process to install SQL.
I am very much open to correction on my theories and would welcome a solution.
Tony
October 20, 2008 at 2:36 am
There is no documented process for changing the names of these groups. My strong recommendation is do not attempt to rename these groups or you could put yourself outside of Microsoft Support.
If you look in the registry at SQL Server items, you will find that some services have a registry key that holds the group name. Other services have a key that holds the SID of the group. There may be other references held in GUID-style keys, and for all I know SQL Server may dynamically generate the group names when it needs them.
If your process of generating a VM includes changing the server name, there is a documented process in BOL for dealing with server name changes. This process will make any changes to the group names that are needed.
If you want to do an unattended install and configuration, the SQL FineBuild process I published on Codeplex can get you there.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply