[fix]-unable to connect to local instance of SQL Server
Scenario:
Error occurs when connecting to PC-NAME\SQLEXPRESS with Windows Authentication as:
Cannot connect to PC-NAME\SQLEXPRESS. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
Error:
A network-related or instance-specific error occurred while establishing a connection to SQL Server
Fix 1:
Select SQL Server Configuration Manager
Check for running state of SQL Server and Browser services.
Go to protocols for SQL Server for Network Configuration.
Right click on TCP/IP and select “Enable”.
Go to Client Protocols.
Right click on TCP/IP and select “Enable”.
Now start SQL Server Management Studio.
Click connect button.
Select Server name dropdown and select “Browse for more” option.
Choose the Server listed under your Database Engine as shown here.
Click ok button to connect to the server.
Fix 2:
Sometime firewall can block port 1433. Disable it as follows:
a) Go to the Start menu -> Control Panel -> Administration Tools -> Services.
b) Find Firewall service, it must be disabled (if it is not, then right click the service and select Stop from the context menu).
Add an exception on the firewall for the SQL Server instance and its port as:
- Type Firewall.cpl in Start -> Run Command.
- Now click on exceptions tab.
- Add the sqlservr.exe (located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Bin), and the port number (default is 1433)
Fix 3:
In Microsoft SQL Server Management Studio right click on the DB server name.
Select “Properties”.
Click on Connections option.
Tick the “Allow remote connections to this server” under “Remote Server Connections”.
Fix 4:
When using a named SQL Server instance ensure that the instance name in specified in the connection strings as machinename\instancename.
Fix 5:
Sometime with SQL Authentication, the user doesn’t have proper access to connect the server. So set the user account and properties as follows:
Setup User Account
- Create a new Login acnt: In SQL Management Studio -> Expand the local Server -> Security -> Right click on Logins -> New Login.
- Set Password settings on New User Account: Uncheck Enforce password policy, password expiration and user must change pw. Select Default database -> select Database name
- Grant roles to New User Account: User Mapping Page -> Map User to DB and grant db_owner role. In Status Page -> Grant Permission to connect and Enable Login.
Setup Access Permissions/Settings for User
- Enable all auth modes: Right click your Local Server -> Properties -> Security Tab -> Enable SQL Server and Windows Authentication Mode
- Enable TCP/IP: Open SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for SQLEXPRESS -> Enable TCP/IP
- Restart SQL Server Service: You will have to restart the SQL Server(SQLEXPRESS) after enabling TCP/IP
Fix 6:
Check whether database engine is installed in the. Manchine.
Fix 7:
Check for SQL Server Browser’s running state in the Services.msc.as follows:
1. Start->Run->Services.msc
2. Find "SQL Server Browser"->Right Click->Properties.
3. Set Startup Type to Automatic->Click Apply->Click start.
4. Retry your connection.
Fixes are applicable to the following versions of SQL Server:
- SQL Server 7.0 OLAP Tools
- SQL Server 2000 64-bit Edition
Related Error Tags:
- Use SQL Server Management Studio to connect remotely to an SQL Server Express instance hosted on an Azure Virtual Machine
- Could not open a connection to SQL Server
- FAQ: SqlException: A network-related or instance-specific error
- ERRMSG: "A network-related or instance-specific error occurred while establishing a connection to SQL Server
"Cannot Connect to Server - A network-related or instance
- Resolving “A network-related or instance-specific error n SQL.
- SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server.
