Knowledge Base

How to specify a strong SA password when you install SQL Server 2000 Desktop Engine (MSDE 2000)

Article ID: 814463

Article Last Modified on 3/22/2007

APPLIES TO

Microsoft SQL Server 2000 Desktop Engine (Windows)

SUMMARY

This article describes how to specify a strong sa password when you install SQL Server Desktop Engine (also known as MSDE 2000).

MORE INFORMATION

You 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 Rules

You 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.

REFERENCES

For more information about how to embed MSDE in a custom application setup, visit the following Microsoft Web site:

Embedding MSDE 2000 Setup in the Setup of Custom Applications

Keywords: kbsetup kbsecurity kblogin kbauthentication kbsample kbguidelines kbcode kbinfo KB814463

Microsoft KB Archive/814463

Contents