Trail Blazer Knowledge Base:


Home : MS SQL Server : SQL MSDE Security and Authentication

Knowledge Base







User:

Password:



Article ID: KB12
Keyword Name:
Created: June 04, 2007
Viewed: 6180

SQL MSDE Security and Authentication

SQL MSDE Security and Authentication

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure that you understand how to restore the registry if a problem occurs.

IN THIS ARTICLE

SUMMARY
MORE INFORMATION
 
Security
 
Users, Groups, and Roles
SA Account Password
Security in ASP
Security Patches
Authentication
 
Windows Authentication
Mixed Mode Authentication
 
Turn on Mixed Mode Authentication During Installation
Turn on Mixed Mode Authentication After You Install MSDE
REFERENCES

SUMMARY

System administrators and developers must consider security and authentication issues because users who are not authorized can destroy, steal, or otherwise gain access to data. This article gives you an overview of Microsoft Desktop Engine (MSDE) Security and Authentication and some helpful tips about how to make your data more secure.

back to the top

MORE INFORMATION

Security

To secure your database, you must understand your users. Users can have many different purposes when they connect to your database. Users can read the data, change the data, delete the data, and insert more data. The first step to secure your database is to decide what activities each user is permitted to perform on the database.

back to the top

Users, Groups, and Roles

SQL Server and MSDE have Users, Groups, and Roles that you can use to control the level of security on the database. If a particular group of users only have to read the data from the database, you can create a group named OnlyReaders, and then add the users to this group. The users who are members of this group can only read the data; they cannot change the data, whether intentionally or by accident.

To learn more about Users, Groups, and Roles, see the SQL Server Books Online. To add Users, Groups, and Roles on an MSDE database, use the OSQL utility.

back to the top

SA Account Password

Another simple step to make your database more secure is to verify that the SA account has a secure password. Many developers and system administrators leave the SA account password blank, which permits anyone to gain access to the database.

To change the SA account password on your MSDE database, follow these steps:

1. On the computer that is hosting the instance of MSDE that you are connecting to, open a command prompt window.
2. Type the following command, and then press ENTER:

osql -U sa

This command connects you to the local, default instance of MSDE by using the SA account.
3. Type the following commands on separate lines, and then press ENTER:

sp_password null
'mynewpassword'
'sa'

NOTE: Replace 'mynewpassword' with the new password.

Note that you receive the following message, which indicates that your password was changed successfully:
Password changed.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

325003 HOW TO: Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility

back to the top

Security in ASP

Security in Active Server Pages is as important as security for Windows-based programs. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

176377 INFO: Accessing SQL Server with Integrated Security from ASP

back to the top

Security Patches

To maintain security, you not only have to manage users, groups, and roles, but you also have to verify that you have installed the latest patches on your database server. Security patches are available for download for SQL Server and MSDE. Microsoft recommends that you install these patches immediately. For more information, visit the following Microsoft Web site:

This is a cumulative patch that includes the functionality of all of the patches that have been released previously for SQL Server 2000. Also, this patch fixes the following three newly discovered vulnerabilities that affect SQL Server 2000 and SQL Server Desktop Engine (also known as MSDE 2000). (These vulnerabilities do not affect any previous versions of SQL Server or MSDE.):

A buffer overrun vulnerability in a procedure that is used to encrypt SQL Server credential information.

A user who is not authorized can use this vulnerability to gain significant control over the database. It is possible for the user to gain control of the server itself, but this depends on the account that SQL Server uses.
A buffer overrun vulnerability in a procedure that relates to the bulk insert of data in SQL Server tables.

A user who is not authorized can use this vulnerability to gain significant control over the database. It is possible for the user to gain control of the server itself.
An administrative credentials elevation vulnerability that occurs because of incorrect rights on the Registry key that stores the SQL Server service account information.

A user who is not authorized can use this vulnerability to gain more rights on the system than system administrator has give to the user's account. It is possible for the user to get the same rights as the operating system. For more information, visit the following Microsoft Web site:

When you install SQL Server 7.0 (including MSDE 1.0), SQL Server 2000, or a service pack for SQL Server 7.0 or SQL Server 2000, the information that you provide for the install process is collected and stored in a setup file named Setup.iss. You can use the Setup.iss file to automate the installation of additional SQL Server systems.

SQL Server 2000 also includes the ability to record an unattended install to the Setup.iss file without your having to actually perform an installation. The administrator who sets up the computer running SQL Server can give a password to the installation routine under the following circumstances:

If you set up SQL Server to have Mixed Mode Authentication, you must give a password for the SQL Server administrator account (the SA account).
Whether you run SQL Server in Mixed Mode or in Windows Authentication Mode, you can require a User ID and a password to start up a SQL Server service account.

In either case, the password is stored in the Setup.iss file. Before the release of SQL Server 7.0 Service Pack 4, the passwords were stored in plain text. For SQL Server 7.0 Service Pack 4 and SQL Server 2000 Service Packs 1 and 2, the passwords are encrypted and then stored. Additionally, during the installation process, a log file is created that shows the results of the installation. The log file includes any passwords that were stored in the Setup.iss file.

back to the top

Authentication

Authentication is a way for SQL Server and MSDE to check logins to verify that the user is permitted to connect to the server. There are two security modes that SQL Server and MSDE use: Windows Authentication and Mixed Mode.

back to the top

Windows Authentication

Windows Authentication uses NTLM to connect to MSDE. If you log on as an Administrator to you computer, MSDE tries to authenticate you as an Administrator.

back to the top

Mixed Mode Authentication

Mixed Mode Authentication permits you to log on to MSDE by using Windows Authentication or SQL Server authentication. SQL Server authentication permits you to create users in MSDE. When you develop programs, you include your user ID and your password in the connection string when you connect to MSDE. For more information about Authentication Modes, visit the following Microsoft Web site:

back to the top

Turn on Mixed Mode Authentication During Installation

During installation, you can change the authentication mode that MSDE uses by running the installation with the following command parameter:

SECURITYMODE=SQL
    

This command parameter causes MSDE to install with Mixed Mode authentication. With this authentication mode, you can connect to MSDE by using Windows Authentication or SQL Server Authentication.

NOTE: By default, for Windows NT and later, MSDE installs by using Windows Authentication. On computers running Windows 98, MSDE uses SQL authentication.

back to the top

Turn on Mixed Mode Authentication After You Install MSDE

WARNING: If you modify the registry incorrectly, serious problems may occur that require you to reinstall your operating system. Microsoft cannot guarantee that the problems can be solved. Modify the registry at your own risk.

Microsoft recommends that you back up the registry before you modify it. If you are running Microsoft Windows NT or Microsoft Windows 2000, also update your Emergency Repair Disk (ERD).

By default, the value of the LoginMode Windows registry subkey is set to 1 for Windows Authentication. To turn on Mixed Mode authentication after you install MSDE, you must change this value to 2. The location of the LoginMode subkey depends on whether you installed MSDE as the default MSDE instance or as a named instance.

If you installed MSDE as the default instance, the LoginMode subkey is located in the following registry subkey:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode
If you installed MSDE as a named instance, the LoginMode subkey is located in the following registry subkey:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name \MSSQLServer\LoginMode

To change the value of LoginMode to 2, follow these steps:

1. In Control Panel, open the Services tool, and then stop MSSQLSERVER and all other related services (such as SQLSERVERAgent).
2. On the Start menu, click Run, type regedt32, and then click OK to start Registry Editor.
3. Locate either of the following subkeys (depending on whether you installed MSDE as the default MSDE instance or as a named instance):
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\

-or-
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\
4. In the right pane, double-click the LoginMode subkey.
5. In the DWORD Editor dialog box, set the value of this subkey to 2, verify that the Hex option is selected, and then click OK.
6. Restart the MSSQLSERVER service and the SQLSERVERAgent service for this change to take effect.

back to the top

REFERENCES

For more information, visit the following Microsoft Web sites:

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

259710 PRB: SQL Server Agent Fails to Start on Windows 9x When You Change the sa Password
319930 HOW TO: Connect to Microsoft Desktop Engine
313418 PRB: Unsecured SQL Server with Blank (NULL) SA Password Leaves Vulnerability to a Worm
285097 INF: How to Change the Default Login Authentication Mode to SQL While Installing SQL Server 2000 Desktop Engine by Using Windows Installer
248683 INF: Microsoft Data Engine Security Recommendations for ISVs
321698 PRB: Cannot Connect to MSDE by Using ADO.NET with SQL Authentication

back to the top


APPLIES TO
Microsoft SQL Server 2000 Desktop Engine (MSDE)
Microsoft SQL Server 7.0 Desktop Edition


Find our more information about Trail Blazer Campaign Services Inc. [click here]


Powered by: ClickCarts KnowledgeBase