[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

SQL server can only connect in Management Studio

seems to be a lot on internet about this error, but can't get connection to work
note the error happens on both same and remote server

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)


scenario:
SQL 2014
database added
both SQL and Windows authentication enabled
both SQL and Windows authentication work in SQL Server Management studio

HOWEVER neither methods (Windows or SQL) works in e.g. Visual studio etc. (either on same or remote LAN Server)
I've tried using IP address and also the domain e.g. me.mysite.com

I have Express and Standard SQL installed, not sure if that would be problem

server name: 1.2.3.4
OR
server name:  me.mysite.com

correct username/password input


SO, how do I get BOTH SQL Management Studio  AND other programs e.g. Visual studio, ASP.Net to connect..thanks
0
rwallacej
Asked:
rwallacej
  • 3
  • 2
1 Solution
 
David Johnson, CD, MVPOwnerCommented:
run sql configuration manager and enable the protocols you want if any changes restart that sql server instance. open port 1433 on the sql server tcp inbound.

With default names sql express instance will be servername\sqlexpress and standard sql will just be servername.  If you are not in the same domain or on the same machine you must use sa authentication because it needs something to authenticate the username password combination with
0
 
rwallacejAuthor Commented:
On SAME server. SSMS connects fine. Client apps or visual studio don't connect
Instance name is MSSQLSERVER2014
Please give example of string for this (if instance name must be included)
I can't think I've seen instance names in connection strings before
Port 1433 & protocols enabled
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Server Browser service need to be run for accept remote connections.
Check if that service is running. If not set it to automatic and start the service.
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
rwallacejAuthor Commented:
given up on this for now, will try another time
0
 
rwallacejAuthor Commented:
turning off the firewall helped and using the x.x.x.x\INSTANCE instead of x.x.x.x helped
0
 
David Johnson, CD, MVPOwnerCommented:
without an instance name it connects to mssqlserver but your instance name is MSSQLSERVER2014 ergo you need to connect to servername\MSSQLSERVER2014  you can re-enable the firewall if port 1433 and 4022 are open for the local network on the server for inbound tcp
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now