Article ID: 889615
Article Last Modified on 3/13/2007
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Workgroup Edition
SYMPTOMS
When you try to connect to Microsoft SQL Server 2000 or SQL Server 2005, you may receive the following error message:
CAUSE
This problem occurs when the SQL Serversecurity authentication is set to Windows only, and one of the following conditions is true:
- You are trying to connect to a SQL Server database with a SQL Server login.
- You are trying to connect to a SQL Server 2000 Desktop Engine (also known as MSDE 2000) database that is installed on a Microsoft Windows 98-based computer by using a trusted SQL Server connection.
- You are trying to connect to a SQL Server database from a Windows account that does not have sufficient permissions to connect to the server.
WORKAROUND
To work around this problem, use one of the following methods depending on the symptom that you are experiencing:
- ==== A SQL Server login ====
Use a valid Windows login to connect to SQL Server. If you must continue to use a SQL Server login, you can change the security authentication mode in SQL Server to SQL Server and Windows. To do this, follow these steps:- Start Enterprise Manager.
- Expand Microsoft SQL Servers, and then expand SQL Server Group.
- Right-click the server that you want to change to SQL Server and Windows authentication, and then click Properties.
- In the SQL Server Properties dialog box, click the Security tab, click SQL Server and Windows, and then click OK.
- When you are prompted to re-start the SQL Server service, click Yes.
Note If you use SQL Server 2005, use SQL Server Management Studio instead of Enterprise Manager to change the security authentication mode.
- ==== A SQL Server 2000 Desktop Engine (MSDE 2000) that is installed on a Microsoft Windows 98-based computer ====
When SQL Server 2000 Desktop Engine is installed on a Windows 98-based computer, you must use a standard SQL Server login to connect to the database. The Windows only authentication mode is not supported when you install SQL Server 2000 Desktop Engine on Windows 98-based computer.
- ==== A Windows account with insufficient permissions ====
To work around this problem, you must add the Windows account to SQL Server, and then grant the appropriate permissions to each database that the user requires access to. To do this in SQL Server 2000, follow these steps:
- Start Enterprise Manager.
- Expand Microsoft SQL Server, and then expand SQL Server Group.
- Expand your server, and then expand Security.
- Right-click Logins, and then click New Login.
- In the SQL Server Login Properties dialog box, enter the Windows account name in the Name box. In the Domain list, select the domain that the Windows account is a member of.
- Click the Database Access tab, set the appropriate permissions for the client, and then click OK.
Note If you use SQL Server 2005, use SQL Server Management Studio instead of Enterprise Manager to change the security authentication mode.
Note After you have added the account, you will still have to grant access to the individual database objects that the user requires access to.
Note Whenever possible, we recommend that you set the SQL Server authentication to Windows only.
MORE INFORMATION
For more information about authentication modes in SQL Server 2000, visit the following Microsoft Network Developer (MSDN) Web site:
For additional information about how to troubleshoot connectivity issues in SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base:
827422 How to troubleshoot connectivity issues in SQL Server 2000
Additional query words: SQL2000
Keywords: kbtshoot kbprb KB889615
