CODEWITHSUNDEEP
sql-server
Sami
Sami

Reputation: 413

I can't connect to a SQL Server instance despite of everything and Browser and Agent cannot be started

When I execute sqlcmd -L I get this:

C:\Users\Lana>sqlcmd -L

Servers:
     ;UID:Login ID=?;PWD:Password=?;Trusted_Connection:Use Integrated Security=?;APP:AppName=?;*WSID:WorkStation ID=?;

I have a feeling this has to do with the fact I cannot connect to SQL Server 2016:

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.

What I have tried so far:

At this point in SQL Server Configuration Manager under SQL Services I have the SQL Server Agent and SQL Server Browser as Stopped (Start Mode = Boot, System, DIsabled and Unknown) and I can't start either one of them from here. In the same screen I have: SQL Server (SQLEXPRESS) running on Automatic.

I still can't connect to this SQL Server instance.

Upvotes: 0

Views: 2650

Answers (2)

SchmitzIT
SchmitzIT

Reputation: 9552

You tried to connect to just the servername, but are using SQLExpress (as can be identified in the path you refered to in your originaL post:

C:\Program Files\Microsoft SQL Server\ MSSQL13.SQLEXPRESS \MSSQL\Binn\sqlservr

When you install SQL Server, you will be given the option to install it to the default instance, or a named instance (and you can combine the two, with the note you can only have 1 (one) default instanced, but a (nearly) unlimited amount of named instances.

I say nearly, because it's limited by the hardware specs of your server. Also, MS has a maximum limit they support, but I do not think there's any hard-coed restrictions to the number of instances you can run.

There's a difference between installing SQL Server Express (which always by default will install itself in a named instance called SQLExpress (as you found out)) and the other editions, which will by default use the default instance (typically, the name of the server, and the path then will be called MSSQLSERVER)).

Upvotes: 1

sepupic
sepupic

Reputation: 8687

Agent cannot be started

You have Express edition and SQL Server Agent is not a part of this (check here for Editions and supported features of SQL Server 2016 under Management Tools)

For unknown reasons when you install Express Edition, the Agent is also installed but there is nothing that you can do to make its work

How should I have known this? What should I have checked?

If it was YOU to istall the server, you was asked about instance name you want. You could choose default instance (that is not default for Express), but you left the default option, i.e. named instance with predefined name SQLEXPRESS

This is the dialog box to choose the name, but in this case it's not Express so the default for it is default instance, not named instance.

pict

If it was not you to install but you can access the pc where it's installed you can check Services to find out service name (there are 3 instances in the picture):

pict2

You could see the instance name directly in Configuration Manager (here the name is TEST1): enter image description here

And you can figure out the instance name even from the directory name where server is installed:

C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Binn\sqlservr

Upvotes: 1

Related Questions