SQL Server does not exist or access denied_问答_开发者

We are currently migrating servers (2005 to 2008) and our host backed up the SQL database and transferred it to the new server.

I have transferred the website files and changed the IP address in the connection string and开发者_StackOverflow社区 the global.asa file.

So technically speaking we have a mirror image on the new server, but when I try to launch a page that connects to the SQL I get the following error.

SQL_Server_does_not_exist_or_access_denied.

Check your TCP/IP Port is enabled or not

To check it:

-- Open SQL Server Configuration Manager from start program.
-- Expand SQL Server Network Configuration
-- Click on Protocols for XXXX
-- Right Click on TCP/IP and open properties
- Enable TCP/IP
- In IP Address Tab, Set Port 1433 in the last option (IPAll)

The error message in this case is very likely correct. First, verify that the network path from you to the new server works and you can connect with the specified userid/pwd.

1) Open Management Studio and attempt to connect to the SQL Server instance by ip address. (By the way, you will probably need to use a SQL Server userid/pwd if you aren't already since you're on an Internet host and there is very likely no AD context for Windows auth.)

2) If you can indeed connect to the instance and your database using Management Studio then you've probably missed a connection string somewhere. Try to maintain only 1 location for said connection string, usually in a config file. Examine the error line reported in detail to discover which connectionstring is being used and to verify that it reflects the new server ip.

This same error also occurs when a cloned server's IP is not configured correctly in SQL Config --> TCP/IP connection.

With misconfigured IP in SQL TCP/IP config does let you:

connect from client machine using server name using ODBC connection
connect using TELNET with 1433 port number with sever name

This happens when the server is cloned from another SQL server which carries the SQL IP config from the primary server as residue.

But client applications fail to connect using connection strings though using the server name with the following message:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

Do the following to verify on the SQL Server:

Note the IP of the server itself (probably admins updated the machine's IP after cloning the box) by doing IPCONFIG
Open SQL Server Configuration Manager
Expand SQL Server Network Configuration
Select Protocols for MSSQLSERVER
Double click TCP/IP on the right
Switch to IP Addresses tab
Scroll down to IPv4 section to verify if it is the same as the IP address of the machine itself. Fix the IP, if it is not. If it is correct this is not the issue.

I experienced this issue as well when setting up an installation on a new computer.

The connection problem I experienced was due to a firewall setting on the server that hosts MSSQL. The setting gave individual IP addresses access to it which 'works' in our environment since IP addresses are described by our administrator as 'sticky'. Once the Firewall access was granted to my new machine's IP address, the PHP odbc_connect(...) statement I was running in my local WAMP setup completed immediately.

As with many problems, there is probably more than one way to run into them, but I wanted to be as explicit as possible about how I experienced it.

This probably would have been a little more clear if I had my own installation of MSSQL management studio on my PC but I do not.

Of the Inbound rules on the server housing MSSQL, I found a rule referring to the MSSQL instance and sure enough found the IP address of my old machine there underneath the properties tab "Scope" as a "Remote IP address". Here's to hoping I remember this whenever I get my next machine! (The decommissioned PC's IP has been removed)

@Darren, if you could leave a comment under the answer you accepted, it might be nice to know which of @Tahbaza's suggestions led you to your resolution.

I experienced this problem when connecting from Qt to Microsoft SQL Server 2014 running also on my computer ...

I used the following QODBC connection string:

db.setDatabaseName("Driver={SQL Server};Server=DESKTOP-F6T7JPD\\sql_Instance_Name;Database=master;Uid=sa;Pwd=your_pwd;");

I only suffered 2 issues:

identifying what to use for Server, this must be the Server Name as configured on SQL server instance, this can be found by opening "MicrosoftSql2014 management Studio", then by looking at properties I found the name (DESKTOP-F6T7JPD\sql_Instance_Name)
the second issue actually is that this name must be modified by adding another back slash \ between my PC name and the instance name so the name found on management studio will be like: PC_Name\Sql_Instance. Note the "\", ... this must be put as Pc_Name\\Sql_Instance.
Note the \\ between PC name and Instance Name!

Are the credentials for your database configured to use SQL users and logins instead of Windows users and logins?

If so, you may need to remap the SQL users in the database to the SQL logins on the server. To scan to see if you have this problem, you can use EXEC sp_change_users_login 'Report'. The 'Auto_Fix' option will automatically correct issues the system finds. You can also run ALTER USER $User WITH LOGIN $Login to manually correct each mapping individually.

http://technet.microsoft.com/en-us/library/ms174378.aspx

The issue is caused because the SIDs generated for the SQL user in the database on the old server don't match the SIDs for the SQL logins on the new database. The stored procedure corrects the discrepancy.

One possible cause is that the server is not running. If you cannot connect even in the Management Studio, check that the SQL Server service is Started and it has been configured as Automatic so that is is started at boot.

If you have access denied error while connecting to SQL server then make sure under SQL server network configuration there are protocols for TCP/IP are enabled.

Please see below screen shot

SQL Server does not exist or access denied