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

Joesph 21 Reputation points
2021-08-27T02:12:22.587+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,705 questions
Accepted answer
  1. Erland Sommarskog 101K Reputation points MVP
    2021-08-29T11:54:47.52+00:00

    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.

    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2021-08-27T03:06:21.033+00:00

    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://learn.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)

    0 comments
  2. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-08-27T03:16:55.237+00:00

    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.

    0 comments
  3. Joesph 21 Reputation points
    2021-08-29T10:56:55.413+00:00

    Hi @Ronen Ariely 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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 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

    0 comments
  4. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-08-30T06:48:32.687+00:00

    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

    0 comments