SQL 2005 Won't Start / Upgrade / Install

I racked my brain for a few hours performing an MS SQL 2005 install on one server and an MS SQL 2005 SP2 upgrade on a second. For both I received errors that, after much Googling and trolling of MS's Technet site, I could find no relevant answer.

Basically, if you see the errors as described below while installing MS SQL 2005 or SP2, or suddenly MS SQL 2005 fails to start up on reboot / service restart, maybe this will help you out.

My issue:

1. On a freshly installed Windows 2003 SP2 server, my installation of MS SQL 2005 would fail if I specified a domain account as the service account to use with the error below in the setup log (seen by canceling the operation and clicking the link provided at the end):




PerfTime Start: Do_sqlScript : Tue Sep 18 11:31:08 2007

Service MSSQLSERVER with parameters '-m SqlSetup -Q -qSQL_Latin1_General_CP1_CI_AS -T4022 -T3659 -T3610 -T4010' is being started at Tue Sep 18 11:31:08 2007

Service failed unexpectedly (1067)

Error Code: 0x8007042b (1067)
Windows Error Text: The process terminated unexpectedly.

Source File Name: sqlsetuplib\service.cpp
Compiler Timestamp: Fri Sep 16 13:20:12 2005
Function Name: sqls::Service::Start
Source Line Number: 301




2. If I installed MS SQL 2005 as LocalSystem then used the SQL Server Configuration Manager to change the service account (per MS KB 283811) the attempt would fail with "WMI Provider Error: Object not found (0x80092004)"


3. If I used the Services control panel to change the service account, after following MS's KB 283811 on manually making the registry and filesystem changes) the service would fail to start with the following in the SQL ERRORLOG (by default "c:\program files\microsoft sql server\mssql.1\mssql\log\errorlog"):




2007-09-18 09:16:24.06 spid9s Starting up database 'model'.
2007-09-18 09:16:24.06 Server Error: 17190, Severity: 16, State: 1.
2007-09-18 09:16:24.06 Server FallBack certificate initialization failed with error code: 1.
2007-09-18 09:16:24.06 Server Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2007-09-18 09:16:24.06 Server Error: 17182, Severity: 16, State: 1.
2007-09-18 09:16:24.06 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80.
2007-09-18 09:16:24.06 Server Error: 17182, Severity: 16, State: 1.
2007-09-18 09:16:24.06 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x1.
2007-09-18 09:16:24.06 Server Error: 17826, Severity: 18, State: 3.
2007-09-18 09:16:24.06 Server 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.
2007-09-18 09:16:24.06 Server Error: 17120, Severity: 16, State: 1.
2007-09-18 09:16:24.06 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.



And the following events would appear in the Event Viewer, Application log:



Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17120
Date: 9/18/2007
Time: 4:44:18 PM
User: N/A
Computer: XXXXXXXXXX
Description:
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:




Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17826
Date: 9/18/2007
Time: 4:44:18 PM
User: N/A
Computer: XXXXXXXXXXXX
Description:
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.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:




Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17182
Date: 9/18/2007
Time: 4:44:18 PM
User: N/A
Computer: XXXXXXXXXXX
Description:
TDSSNIClient initialization failed with error 0x80092004, status code 0x1.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:




Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17182
Date: 9/18/2007
Time: 4:44:18 PM
User: N/A
Computer: XXXXXXXXXXX
Description:
TDSSNIClient initialization failed with error 0x80092004, status code 0x80.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:



Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17190
Date: 9/18/2007
Time: 4:44:18 PM
User: N/A
Computer: XXXXXXXXXXX
Description:
FallBack certificate initialization failed with error code: 1.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:



Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 15466
Date: 9/18/2007
Time: 4:44:18 PM
User: N/A
Computer: XXXXXXXXXXXX
Description:
An error occurred during decryption.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:




4. Even more perplexing was if I used LocalSystem or the local administrator's account, or my own domain account, the installation would proceed fine or the SSCM would properly change the account.

For me the problem was that we were deploying a custom "default user" profile to these servers. The error resulted because when our service account logged in, it's profile was created in "C:\Documents and Settings" for the first time as a copy of the "Default User" profile stored there. There were actually two issues with the "Default User" profile we created:

1. On one of the servers, the permissions were wrong for "C:\Documents and Settings\[service account]Application Data\Microsoft\Crypto\RSA" (the Server\Administrators group had no access).


2. On a second server, the "Default User" profile didn't contain an "NTUSER.DAT" file (apparently it was in use during the deployment of the profile and wasn't copied in

When we used my domain account or the local administrator account, since we had already logged in we had a previously-existing profile with the correct rights and, obviously, with an NTUSER.DAT file.

My resolution:

• On the server with incorrect permissions: grant local Administrators group full access to C:\Documents and Settings\[service account]\Application Data\Microsoft\Crypto


• On the server with a missing NTUSER.DAT file: I also copied in the original (backed up) "Default User\NTUSER.DAT" file to the service account's profile folder.

Additional issues:
I've seen this Default User fiasco also affect SSL certificate assignments in IIS if the service account the application pools are running under suffer from the same issues as listed above.

Powershell script to create Remote Desktop connection files

I have several hundred servers I manage. I like to keep a folder on my "Quick Launch" bar called "RDP" with all the connection files to the servers contained within, broken down by environment.

After the first time of needing to change one setting, I decided to script the creation of these files.

Prerequisites:

1. PowerShell 1.0 installed (which requires .NET Framework 2.0)
2. Windows XP or Server 2003 (not tested on 2000)

What this script does:

1. It creates subfolders based on predefined resolutions (800x600, 1024x768, and fullscreen). I added this because I sometimes control my work machine from home which has a lower resolution (and I rarely like working fullscreen)
2. It creates subfolders organized as specified in a comma-delimited file to create the RDP files in, so I can have one for each environment we have (QA, Staging, Production, etc)
   
3. It deletes existing RDP files within the folder structure before creating new ones. Note, however, I do not delete all RDP files. If you remove an entry from the CSV file, it will remain unless you delete the subfolders manually.
   

Script files

• You can download everything mentioned in this article by clicking here: CreateRDP_7.9.23.rar
• Note: the above archive contains an example shortcut hard-coded based on my computer. You'll want to edit it or create a new one.
  

Steps:

1. Create a folder to contain the RDP files. We'll use "RDP" here
2. Within "RDP" create a folder called "_script"
3. Within "RDP\_script" create the following:
1. A comma-delimited file called "rdplist.csv" with the following column headings: "ServerName", "IP", "Directory" (first row should read: "ServerName,IP,Directory" if using a text-editor). For each row, fill in the proper values for each server. "IP" can be either the IP address or FQDN. "Directory" refers to the subfolder of "RDP" that will be created for this server.
   
2. A text file called "CreateRDP.ps1" with the following (take special care to preserve the back-tick-N "`n" as that is the PowerShell code for a new line, necessary for adding into the RDP file):



$List = Import-CSV RDPLIST.CSV
$resolutions = ("fullscreen","800x600","1024x768")
ForEach($Entry in $List) {
ForEach($resolution in $resolutions) {
# Prepend the destination directory info for RDP files
$Dir = "..\" + $resolution + "\" + $Entry.Directory

# Create new folder
New-Item -Path $Dir -ItemType Directory -Force

# Build the file name
$FileName = $Dir + "\" + $Entry.ServerName + ".RDP"

# Remove the old file
Remove-Item $FileName -Force

# Begin building RDP file
$temp = "`nfull address:s:" + $Entry.IP

switch ($resolution) {

"fullscreen" {
$temp = $temp + "`nscreen mode id:i:2"
}

"800x600" {
$temp = $temp + "`nscreen mode id:i:1"
$temp = $temp + "`ndesktopwidth:i:800"
$temp = $temp + "`ndesktopheight:i:600"
}

"1024x768" {
$temp = $temp + "`nscreen mode id:i:1"
$temp = $temp + "`ndesktopwidth:i:1024"
$temp = $temp + "`ndesktopheight:i:768"
}
}
$temp | out-file $FileName
write-host $temp
get-content template_bottom.txt >> $FileName
}
}



4. Create a shortcut of the script and save it under "RDP" taking care that the "Start In" folder is still "RDP\_script". This is necessary if you want to just be able to click the file and have it create / update your RDP files. Otherwise, PowerShell will complain about not finding the script and I prefer to not hardcode paths within the script.
5. Create a text file called "template_bottom.txt" and save it in "RDP\_script". This file, and example of which is below, should contain all of the RDP settings you want to use EXCEPT the actual server IP/FQDN and screen resolution. A good way to get these values is to create an RDP profile with all the settings you want and then save it here without the server IP/FQDN and screen resolution line (see script above for which to remove):




audiomode:i:2
authentication level:i:0
autoreconnection enabled:i:1
bitmapcachepersistenable:i:1
compression:i:1
disable cursor setting:i:0
disable full window drag:i:1
disable menu anims:i:1
disable themes:i:0
disable wallpaper:i:1
displayconnectionbar:i:1
keyboardhook:i:2
redirectcomports:i:0
redirectdrives:i:1
redirectprinters:i:0
redirectsmartcards:i:0
session bpp:i:16



6. Double-click the shortcut within "RDP" and see how it works!

Note, you may have to enable script execution. Powershell by default does not automatically execute scripts. Microsoft, being overly paranoid after .vbs files being used as trojan and virus entry points, set the default execution policy for Powershell scripts to "deny". To enable scripts, you'll need to either sign the scripts with a trusted certificate or, as I do, just enable script execution:
"PS c:\Set-ExecutionPolicy Unrestricted"

For those who asked how to save credentials in this script, EverydayNerd has made an improvement on my script which does just that. Check it out: http://everydaynerd.com/microsoft/save-password-in-rdp-file