An error occurred while establishing a connection with the server. When you connect to SQL Server 2005, the default setting does not allow remote connection to SQL Server may cause this failure.
(Provider: SQL network interface, error: 26-An error occurred while locating the specified server/instance) (. NET sqlclient data provider) solution:
Step 1: enable remote connection on the sqlserver instance
1. Point to "start-> Program -> Microsoft SQL Server 2005-> Configuration tool-> SQL Server peripheral application configurator"
2. on the "SQL Server 2005 peripheral application configurator" page, click "service and connected peripheral application configurator"
3. Click expand database engine, select remote connection, and select local connection and remote connection on the right ",
Select the Protocol to use (TCP/IP and named pipe service should be enabled here !) Click "application" and you will see the following message:
"The changes made to the connection settings will not take effect until the Database Engine service is restarted .", Click OK to return
4. Expand "Database Engine", select "service", click "stop" on the right side, and wait until the MSSQLServer service is stopped,
Click Start to restart the MSSQLServer service.
Step 2: Enable the sqlserver Browser Service
1. Point to "start-> Program-> Microsoft SQL Server 2005-> Configuration tool-> SQL Server peripheral application configurator"
2. on the "SQL Server 2005 peripheral application configurator" page, click "service and connected peripheral application configurator"
3. Click "SQL Server Browser" and select "service ".
Select "automatic" for "Start type" on the right ",
Click Start and then click OK to return
Step 3: create an exception for "SQL Server 2005" in Windows Firewall
1. on the Windows Firewall settings page, select the "exceptions" tab and click "add program"
2. In the add program window, click browse"
3. Then find "C:/ProgramFiles/Microsoft files/Microsoft SQL Server/mssql.1/MSSQL/binn/sqlservr.exe ",
Click OK to return
Note: the path may vary depending on SQL Server 2005 installation. Mssql.1 is a placeholder that corresponds to the database instance id.
4. Repeat steps 1 to 3 for each SQL Server 2005 instance that requires remote access.
Problem 2: The SA cannot be logged on. The solution is as follows:
Log On with Windows identity authentication. In the "properties" window, go to "security" (Security, in "Server Authentication", set it to "SQL Server and Windows Authentication Mode", OK,
As prompted, you should restart the SQL service.
If
Exec sp_password null, null, 'sa'
Alter login SA enable
The following error is reported:
MSG 15118, level 16, state 1, line 1
Password validity Verification Failed. The password is not complex enough and does not comply with Windows policy requirements.
It indicates that your server's password policy requires complexity (the Password Complexity of SQL 2005 is associated with the password complexity policy of windows ), you need to set the SA password to a complex password, so you have to change it to the following statement:
Exec sp_password null, 'guoqiang1234', 'sa'
Alter login SA enable
-- Sp_password Syntax:
-- Sp_password [old password], <new password>, [logon name]
Problem 3: IP addresses cannot be connected
When using. Net for development, you may encounter a problem where you can use the connection string to connect to the SQL Server 2005 database using the machine name and localhost, but the IP address cannot be connected,
The solution is to enable local and remote connections on the SQL server instance, and use the TCP/IP and named pipeline services when selecting the protocol.
All great actions and thoughts have a negligible start. I am learning the knowledge and hope to succeed! Don't give up on me. I 'd like to make friends all over the world!
