MSSQL2005 solutions that are not accessible after the Win2008 installed in the _mssql2005

Source: Internet
Author: User
Tags microsoft sql server management studio sql server management sql server management studio

For a long time the author did not come here to write things, because really busy.

Recently I have been using the Win2008 system, but found a very strange problem, that is, after the installation of SQL2005 on the system, and then access to the other computer on the host is not accessible.

SQL2005 is also installed on other hosts, but this error message occurs when the SQL on the Win2008 system is connected.

At that time the author is very confused, is why?
Because the Win2003 system, there is never such a problem happen.
The wrong task of the author is that the Win2008 system is incompatible with the SQL2005 system, or there is a mismatch problem which causes the problem to occur.
After a long time of thinking, the author has a lot of solutions.
One of them is to install SQL2008 on the Win2008 system.
This kind of system that belongs to the same age, should not appear this kind of problem.

This is supplemented by other netizens:

Problem with VS2008 connection sql2005 (provider: Named pipe provider, error:40-cannot open connection to SQL Server)
The server was not found or could not be accessed. Please verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider:named pipes provider, error:40-cannot open a connection to SQL Server)

Main process:
1. In the Visual Web Developer, switch to Design view.
2. From the Data folder in the Toolbox, drag the GridView control onto the page.
3. If the GridView Tasks shortcut menu is not displayed, right-click the GridView control, and then click Show Smart Tags.
4. On the GridView Task menu, in the Select Data Source list, click < new data source >.
The Data Source Configuration dialog box appears.
5. Click "Database".
This specifies that you want to obtain data from a database that supports SQL statements. Such databases include SQL Server and other OLE-DB-compliant databases.
In the Specify ID for data source box, the default data source control name ("SqlDataSource1") is displayed. You can keep this name.
6. Click OK.
7. The Configure Data Source wizard appears with a page in which you can select a connection.
Click New Connection.
8. In the Select Data Source dialog box, under Data sources, click Microsoft SQL Server, and then click Continue.
The Add Connection dialog box appears.
9. In the Server name box, enter the name of the SQL Server that you want to use.
10. In the logon credentials, select the option that can be used to access the SQL Server database (integrated security or specific IDs and passwords) and enter a username and password if needed.
11. Click Select or enter a database name, and then enter Northwind.
12. Click Test Connection and click OK when you are sure that the connection is active.
The Configure Data Source-<DataSourceName> wizard is displayed with the connection information filled in.
......
That's the 12th step. Click Test connection out of question, Prompt for error:
An error occurred while establishing a connection to the server. When you connect to SQL Server 2005, this failure may be caused by SQL Server not allowing remote connections under the default settings. (provider: Named pipe provider, error:40-cannot open a connection to SQL Server)

This problem has been encountered before, resolved, but do not know why and come out. The workaround is as follows:
1. If the server opens a firewall, turn it off
2. Open the Sql2005 remote connection function, the opening method is as follows,
The Configuration tool->sql the server perimeter configuration-> service and the perimeter configuration of the connection-> open the database Engine node under the MSSQLSERVER node, select Remote Connection, and then recommend "use tcp/simultaneously" IP and Named pipes ", when you are sure, restart the database service.
3. The login settings are changed to, SQL Server and Windows authentication are selected at the same time, as set out below:
Management Studio Manager->windows authentication (first time in Windows),-> Object Explorer Select your data server--right > Properties >security >sql server and Windows authentication are selected at the same time.
4. Set a user name and password in SQL Server mode, as set out below:
Management Studio Manager->windows authentication>new query>sp_password null, ' sa123456 ', ' sa '
This sets a user named SA, the password is: sa123456 users, the next time you log in, you can use SQL Server method,
User name is SA, password is: sa123456 user into the database.
5. After the above three steps, so write the connection string can be successfully entered the database,
(Server=.\\sqlexpress;uid=sa;pwd=sa123456;database=master ");
Later use this manager to discover, the correct server name is such, machine name \sqlexpress instead of Localhost,127.0.0.1,fancyboy (machine name) Like mine is like this
Server:fancyboy\sqlexpress
The original is: Server=localhost;database=user;uid=sa;pwd= '
Now it's: server=.\\sqlexpress;database=user;uid=sa;pwd= '//I just had a problem.
Another possible workaround was found:
1. Open IIS Properties-"virtual Directory Properties-" Select NET 2.0-"point ' edit Configuration '-" Delete all connected SQLExpress items
Of course, it's best to delete the global configuration,
2. Open IIS Properties-"Site Properties-" Select NET 2.0-"Edit global Configuration"
-"Deletes all connected sqlexpress items.


After the above setting, there is an error:
SQL Server 2005 failed with SA logon-prompts the user to be unrelated to a trusted SQL server connection
Error:
     SA logon lost Failed, prompting the user to have no association with a trusted SQL server connection
Workaround:
1. Open SQL Server Management Studio Express, right-click the server, select Properties, Click Security (Secure) in the pop-up window to switch to Security panel and modify server authentication servers authentication from Windows Authentication Mode (Windows user authentication model) to SQL Server and Windows Authentication mode (SQL Server and Windows Authentication modes), OK.
2. Open Security (Secure)--logins (login name), right-click the SA, select Properties, click Status to switch to Status panel, and login (login) set to Enabled (enable).
(Note: Restart the database when you are done!)
     through the above settings can be done. If not, please check other reasons, such as firewall and other factors   

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.