How to specify a strong SA password when you install SQL Server 2000 Desktop Engine (MSDE 2000) (814463)
The information in this article applies to:
- Microsoft SQL Server 2000 Desktop Engine (MSDE)
SUMMARYThis article describes how to specify a strong sa password when you install SQL Server Desktop Engine (also known
as MSDE 2000).MORE INFORMATIONYou must assign a strong password to the sa account during the installation of any instance of SQL Server
2000 Desktop Engine (MSDE 2000). You must do this even if the instance is using
Windows Authentication Mode. The sa account cannot be used by any user when running in Windows
Authentication Mode; however, the instance can later be switched to Mixed Mode,
and the sa account becomes an active login. If the sa account has a null, blank, simple, or well-known password when an
instance of MSDE 2000 is switched to Mixed Mode, the MSDE instance can then be
accessed by unauthorized users. The sa account cannot be dropped, and it must always be protected with a
strong password to help restrict unauthorized access. Any user who gains access
to an instance of MSDE 2000 by using the sa account, might gain full control on that instance of MSDE, and
have the ability to access any resources that the MSDE service account has. By
default, the MSDE service account is the LocalSystem built-in
security account. For more information about strong passwords, visit
the following Microsoft Web site: Security
RulesYou can use custom application code to install MSDE.
The application code must use one of the following two methods for setting the sa password:
- If the user is going to set up MSDE in Mixed Mode, and is
going to use the sa account, request a strong password for the sa account from the user. Use that password in the MSDE
setup.
- If the sa account is not used, generate a random string, and then pass that
string as the sa password to the MSDE setup.
To help improve security, you should not hard-code the sa password assigned at setup as a parameter in a Setup.ini file, or
as a command prompt switch in a command (.cmd) file, or include it as a
property in an MSI file, or in any other way that can expose the password as
plain text. The password should be dynamically generated by an application
setup program at run time, and it should be passed to the MSDE setup process in
one of the following ways:
- Run the MSDE setup.exe from the application setup code, and
specify a SAPWD value in the arguments.
For example, run the setup by
using the .NET Framework Process class, and then specify SAPWD in the ProcessStartInfo Arguments property, or run the setup by using the Win32 CreateProcess function, and then specify SAPWD in the lpCommandLine parameter.
For more information about the SAPWD command line
parameter, click the following article number to view the article in the Microsoft Knowledge Base:
810826
New switches in MSDE Service Pack 2 Setup
- Perform a custom action to pass a strong password when you
use the MSDE merge modules in a custom Windows Installer-based
setup.
Note You cannot set a password for the sa account during the MSDE 2000 setup by using Windows
Authentication Mode. In this scenario, you must set the password after the
setup completes. Microsoft strongly recommends that you use the latest service
pack to install MSDE 2000. The method that Microsoft recommends you
use to generate a random password is to use the Crypto API functions such as: - CryptAcquireContext
- CryptGenRandom
- CryptCreateHash
- CryptHashData
If you are using native code, use CryptReleaseContext. If you are using managed code, use System.Security.Cryptography.RNGCryptoServiceProvider to obtain a random encoded string, and then hash the value that
is returned by using the ComputeHash method of the System.Security.Cryptography.SHA1 class. The random string must be of variable length, between 7
and 20 characters. If you forget the sa password, or you do not know what the sa password is, and the instance is converted to Mixed Mode, a
member of the sysadmin fixed server role can reset the sa password without knowing the previous password. By default, all
users who are members of the local Administrators group are members of the sysadmin role. The members of the sysadmin role can change an MSDE instance from Windows Authentication Mode
to Mixed Mode or vice versa, and can change the sa password. Hence, for security reasons, you may want to remove the
Administrators group from sysadmin role.
For more information about how the Administrators group can be
removed from the sysadmin role, click the following article number to view the article in the Microsoft Knowledge Base:
263712
How to impede Windows NT administrators from administering a clustered instance of SQL Server
For more information about changing the password for the sa
account, click the following article number to view the article in the Microsoft Knowledge Base:
322336
How to verify and change the system administrator password in MSDE or SQL Server 2005 Express Edition
Note The methods for changing the sa password during installation listed in this article only apply to
new installations of MSDE. The following steps use sample source code
to generate a random sa password, and then start an MSDE installation. Using Microsoft Visual C++ .NET- Click Start, point to All
Programs, point to Microsoft Visual Studio .NET,
point to Visual Studio .NET Tools, and then click
Visual Studio .NET Command Prompt.
- Open Notepad.
- Paste the following code in Notepad::
#pragma once
#define WIN32_LEAN_AND_MEAN // Exclude rarely-used stuff from Windows headers.
#define UNICODE
#include <stdio.h>
#include <windows.h>
#include <wincrypt.h>
#ifdef UNICODE
#define STRNCPY wcsncpy
#else
#define STRNCPY strncpy
#endif
#include <atlenc.h>
#define SAPWDSWITCH _T("SAPWD=")
#define INSTANCENAME _T("INSTANCENAME=MSDETEST")
BOOL GenPwd(TCHAR*, int);
void DisplayError (DWORD);
int main(void)
{
//Generate random length for password, between 7 and 20 characters.
int nPwdLen = ((rand() % 20) + 7) + 1; //Extra character for null terminator
TCHAR* pPwd = new TCHAR[nPwdLen];
UINT uRes = 0;
DWORD dwRes = 0;
if (!GenPwd(pPwd, nPwdLen))
{
//Failed to generate a password, log the error and return failure.
dwRes = GetLastError();
DisplayError(dwRes);
return dwRes;
}
STARTUPINFO si;
PROCESS_INFORMATION pi;
ZeroMemory( &si, sizeof(si) );
si.cb = sizeof(si);
ZeroMemory( &pi, sizeof(pi) );
//Allocate a string for the command line.
LPTSTR lpCommand = new TCHAR[nPwdLen + _tcslen(INSTANCENAME) + _tcslen(SAPWDSWITCH) + 2];
_stprintf(lpCommand, _T("%s %s%s"),INSTANCENAME, SAPWDSWITCH, pPwd);
// Specify the complete path of Setup.exe.
if (!CreateProcess(_T("setup.exe"), lpCommand, NULL, NULL, FALSE,
0, NULL, NULL, &si, &pi))
{
dwRes = GetLastError();
DisplayError(dwRes);
}
return 0;
}
//Generates a Random string of length nLen - 1. Buffer ppwd must allocate an extra character for null terminator.
//Returns TRUE if successful, FALSE if fails.
//Extended error information can be obtained from GetLastError().
BOOL GenPwd(TCHAR* ppwd, int nLen)
{
BOOL bResult = FALSE; //assume failure
HCRYPTPROV hProv = NULL;
HCRYPTHASH hHash = NULL;
//Storage for random string 4 times longer than the resulting password.
DWORD dwBufSize = nLen*4;
DWORD dwSize = Base64EncodeGetRequiredLength((int)dwBufSize);
LPSTR pEncodedString = NULL;
LPBYTE pRandomBuf = NULL;
TCHAR* pTRandomPwd = NULL;
try
{
pEncodedString = new char[dwSize];
pRandomBuf = new BYTE[dwBufSize];
// Try to acquire context to Crypto provider.
if (!CryptAcquireContext(&hProv, NULL, NULL, PROV_RSA_FULL, CRYPT_SILENT))
{
if (GetLastError() == NTE_BAD_KEYSET) //Test for non-existent keyset
{
if (!CryptAcquireContext(&hProv, NULL, NULL, PROV_RSA_FULL, CRYPT_SILENT | CRYPT_NEWKEYSET))
throw(GetLastError());
}
else
throw(GetLastError());
}
//Generate a random sequence.
if (!CryptGenRandom(hProv, dwBufSize, pRandomBuf))
{
throw(GetLastError());
}
//Get a handle to a hash, then hash the random stream.
if (!CryptCreateHash(hProv, CALG_SHA1, 0, 0, &hHash))
{
throw(GetLastError());
}
if (!CryptHashData(hHash, pRandomBuf, dwBufSize, NULL))
{
throw(GetLastError());
}
//Destroy the hash object.
CryptDestroyHash(hHash);
//Release Provider context
CryptReleaseContext(hProv, 0);
//Encode the hash value to base64.
if (!Base64Encode(pRandomBuf, dwBufSize, pEncodedString, (int*) &dwSize, 0))
{
throw(GetLastError());
}
//Determine how many tchars you need to convert string to base64.
int nTchars = (int) strlen(pEncodedString);
pTRandomPwd = new TCHAR[nTchars];
#ifdef UNICODE
if (MultiByteToWideChar(CP_UTF8, 0, pEncodedString, nTchars, pTRandomPwd, nTchars) == 0)
{
throw(GetLastError());
}
#else
STRNCPY( pTRandomPwd, pEncodedString, nLen);
#endif
//Copy the first x characters of random string to output buffer.
STRNCPY(ppwd, pTRandomPwd, nLen);
//Add null terminator to ppwd string.
ppwd[nLen] = _T('\0');
bResult = TRUE;
}
catch (DWORD)
{
//Set return value to false.
bResult = FALSE;
}
catch (...)
{
//Unknown error, throw.
throw;
}
//Clean up memory.
if (pRandomBuf)
{
delete pRandomBuf;
pRandomBuf = NULL;
}
if (pEncodedString)
{
delete pEncodedString;
pEncodedString = NULL;
}
if (pTRandomPwd)
{
delete pTRandomPwd;
pTRandomPwd = NULL;
}
return bResult;
}
void DisplayError (DWORD dwError)
{
//Resolve the error code to a message string.
LPCTSTR MessageBuffer;
DWORD dwBufferLength = FormatMessage( FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_IGNORE_INSERTS | FORMAT_MESSAGE_FROM_SYSTEM,
NULL, // module to get message from (NULL == system)
dwError,
MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), // Default language.
(LPTSTR) &MessageBuffer,
0,
NULL
);
DWORD dwBytesWritten;
// Output message string on stderr.
WriteFile(
GetStdHandle(STD_ERROR_HANDLE),
MessageBuffer,
dwBufferLength,
&dwBytesWritten,
NULL
);
}
- Save the file as StrongSA.cpp.
- At the command prompt, type the following command to
compile the code:
cl strongSA.cpp - At the command prompt, type the following command to run
the code:
strongSA.exe
Using Microsoft C#.NET- In Visual Studio .NET, create a new Visual C# Console
Application project.
- Paste the following code in the class file that contains
the Main function.
Verify that the code replaces all the existing
code in the file:using System;
using System.Diagnostics;
using System.IO;
using System.Resources;
using Microsoft.Win32;
using System.Security.Cryptography;
class InstMSDE
{
static void Main(string[] args)
{
try
{
// Generate random password.
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
byte[] encodedSeed = new byte[512];
rng.GetBytes(encodedSeed);
SHA1 sha1 = SHA1.Create();
byte[] hashval = sha1.ComputeHash(encodedSeed);
String base64HashVal = Convert.ToBase64String(hashval);
// Trim "=" off the end.
base64HashVal = base64HashVal.TrimEnd('=');
string msdeINI = "setup.ini";
// You have to set startInfo parameters values as appropriate for your installation.
ProcessStartInfo startInfo = new ProcessStartInfo();
// Setup.exe for MSDE sp3.
startInfo.FileName = "setup.exe";
// Pass the SA password to the setup program.
startInfo.Arguments = "/settings \"" + msdeINI + "\"" + " SAPWD=" + base64HashVal + " /qr+ ";
startInfo.WindowStyle = ProcessWindowStyle.Normal;
// Substitute the workdir with complete path of installation folder.
startInfo.WorkingDirectory = "c:\\Workingdir";
Process.Start(startInfo);
}
catch (Exception e)
{
Console.WriteLine("Unable to execute program due to the following error: " + e.Message);
return;
}
}
}
- Press F5 to compile, and then run the program.
REFERENCESFor more information about how to embed MSDE in a custom
application setup, visit the following Microsoft Web site:
Modification Type: | Minor | Last Reviewed: | 12/29/2005 |
---|
Keywords: | kbsetup kbSecurity kblogin kbAuthentication kbSample kbGuidelines kbcode kbinfo KB814463 kbAudDeveloper |
---|
|