question

Joesph-5205 avatar image
0 Votes"
Joesph-5205 asked Joesph-5205 commented

How do i get Microsoft SQL Server Managment Studio 18 to connect to a Server

For some reason in the connect to server box when I enter my system name into the server name area, it will not work.

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Joesph-5205,

How are things going?
Do the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·

Thanks. The answers were very helpful. All sorted now.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

It's unclear what you are trying to connect to, but it sounds like all you have downloaded is SQL Server Management Studio. That alone will take you nowhere. You also need an SQL Server instance to connect to.

You can download a free edition of SQL Server, Developer Edition or Express Edition. The difference between these two is that Developer has all features that you find in Enterprise Edition, but it is not licensed for production. Express Edition is quite stripped down, and supports a max database size of 10 GB. On the other hand it is licensed for production

Another option is to create an Azure SQL Database in the cloud.

See further here for details and download links: https://www.microsoft.com/en-us/sql-server/sql-server-downloads.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered

Good day,

It could help if you provided the error messages and screenshot of what you do (without the sensitive information)

when I enter my system name into the server name area, it will not work.

If your SQL Server is not installed in the same machine as your SSMS, then you need to connect remotely to the server. In this case you cannot use a local system user but domain use or SQL Server user. If this is the case, then please clarify. For now, I will assume that they are installed in the same machine.

Your operating system user does not necessarily has the permission to connect the server (by default it is not).

(1) Make sure that you are using the system administrator and try to connect the server (open SSMS as administrator)

(2-1) If this worked then you need to add a LOGIN for your system user.

 CREATE LOGIN [<machine or domain name>\<your system user>] FROM WINDOWS

(3) Next you create USER for this LOGIN in specific databases or make this LOGIN sysadmin. In anyway make sure the user have the permission for whatever you need.

(2-2) Id you cannot connecty using the operating system administrator then check this document for fixing this issue

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-ver15



Note! When you installed the server then you had an option select the authentication mode: (1) Windows Authentication mode, or (2) SQL Server and Windows Authentication mode. If you selected the second option then you can connect the server using the sa user (assuming you remember the password)


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi Joesph-5205,

Welcome to Microsoft Q&A.
Could you please share us the detailed error message?
Is your SQL Server a default instance or a named instance?
You can go to the SQL Server configuration Manager->SQL Server Services to check the Server instance name. If the SQL Server instance is shown as SQL Server(MSSQLSERVER), it means it is a default instance and you only need to enter the host name to connect to SQL Server..
If the SQL Server instance is displayed as SQL Server(intancename), then it means it is a named instance, and you need to use HOSTNAME\INSTANCENAME to connect to SQL Server.

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joesph-5205 avatar image
0 Votes"
Joesph-5205 answered

Hi @pituach and @AmeliaGu-msft
Thanks. I downloaded SQL Server Installation center from Azure and then use the center to download Microsoft SQL Server Management Studio. I was unable to find SQL Server Configuration Manager. Also, I assume my SQL server is a default instance but unsure as I had no option to select on download. I did try as to run Microsoft SQL Server Management Studio as administrator but I got the same problem
Joesph


Error Message:



Cannot connect to ...

===================================

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)

Error Number: 2
Severity: 20
State: 0



Program Location:

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

The system cannot find the file specified

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi Joesph-5205,
Thanks for your reply.

I downloaded SQL Server Installation center from Azure and then use the center to download Microsoft SQL Server Management Studio.

Did you install SQL Server after downloading the SQL Server Installation Center?
If you didn’t install SQL Server, please refer to this step-by-step article which might be helpful.

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.

If you installed SQL Server, this error usually means that the client can't find the SQL Server instance. This normally happens when at least one of the following problems exists:

  • The name of the computer hosting the SQL Server

  • Instance doesn't resolve the correct IP

  • The TCP port number isn't specified correctly

Please refer to this troubleshooting article for more details.

In addition, you can go to C:\Windows\SysWOW64\SQLServerManager<version>.msc (such as SQLServerManager15.msc for SQL Server 2019 (15.x)) to find SQL Server configuration manager.

Best Regards,
Amelia


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

StacyClark-5656 avatar image
0 Votes"
StacyClark-5656 answered

I had a similar issue, and these steps worked for me:

  • Open "SQL Server Configuration Manager"

  • Now, Click on "SQL Server Network Configuration" and Click on "Protocols for Name"

  • Right Click on "TCP/IP" (make sure it is Enabled). Click on Properties

  • Now Select "IP Addresses" Tab -and- Go to the last entry, "IP All."

  • Enter "TCP Port" 1433.

  • Now Restart "SQL Server .Name." using "services.msc" (winKey + r)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered Joesph-5205 commented

Good day @oesph-5205 ,

Did you solve the issue or do you need more help?

We are waiting for your respond on points that were suggested (my guess is that Erland is right and you simply installed the SSMS and not the SQL Server - these are totally different applications. One is the database server which is based on services with no GUI interface, and the other is a client application which is used to connect the server. If this is the case, then please mark Erland's message as the answer :-)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your help. The issue was solved.

0 Votes 0 ·