SQL Server Management Studio

databases
ssms
microsoft-sql

#1

This topic will show how to connect to SQL Server Management Studio (SSMS for short).

What is SQL Server Management Studio?

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure. Use SSMS to access, configure, manage, administer, and develop all components of SQL Server, Azure SQL Database, and SQL Data Warehouse. SSMS provides a single comprehensive utility that combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server for developers and database administrators of all skill levels.

Source: Microsoft SQL

There are different verions of SSMS, so you always want to be sure to attempt connecting with the same version of SSMS as the server version of SQL that you’re using. Since we recommend everyone stay up to date and take advantage of the new features / tools in the later version we will just go over the latest SQL 17 SSMS tool.

Note: SSMS 2017 supports connecting to database egines that run between SQL versions 2008 - 2017, so this tool will work for connecting to your database most likely unless you’re running a version prior to 2008.

As of SQL17 this tool is a stand-alone product and isn’t included during the install of SQL Server. You can find the download page for this product at the URL below:

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Login to SQL Server Management Studio

Locally on own dedicated server:

If you are on our shared server platform or just want to connect to the database server from your own computer machine than skip to the next section.

  1. Login to your VPS via remote desktop (RDP).

    Note: Your servers RDP information is supplied to you within your account information welcome email. Please check the inbox for the e-mail address on file for this information. If needed you can view our topic on viewing these emails directly within your billing control panel using the view emails feature.

  2. Once logged into the server via RDP you might see the SSMS icon pinned to the taskbar. If so, then click this icon. If not, then proceed to step 3.

    SSMS Icon


  1. Click the start menu and type ‘SSMS’ (without the quotes), then choose the option for “SQL Server Management Studio”.

    Note: I recommend that you pin this tool to your taskbar or start menu for easy access.

    SSMS

    You should now be at the login screen for SSMS and see something similar to the below screenshot:

    SSMS


  1. To connect to your database instance enter in the server name of the instance of SQL you’re wanting to connect to. The most common setup is a single instance of SQL on the local server, so for the server name field you may be able to enter ‘localhost’.

    The ‘Authentication’ drop-down should be changed to SQL Server Authentication so that you can connect with the ‘sa’ user for full DB administrator access. The ‘sa’ user by default is set to the use the same password as the windows administrator user, so if needed please view the email containing your account information for this server to obtain the correct credentials.

    Note: If your connecting from one of your servers and attempting to connect to a separate server such as your database server please make sure that your SQL Server is set to allow remote connections to SQL from the server that you’re connecting from. You can see our tutorial topic on managing the server firewall via the WCP control panel if needed.

    You can also view our tutorial topic on creating and managing databases and users via the WCP control panel if needed.

Connect remotely from your own computer:

As mentioned in the first section we recommend that you download and install the latest version of SSMS (Verson 17.x). Once installed open SQL Management Studio:

Opening SQL Management Studio might vary depending on the operating system (OS) that your computer is using, however most machines you can follow the steps below:

  1. Click on your start menu on your local computer. If using an OS that automatically brings up the ‘search’ feature type ‘SSMS’ (without the quotes). Then click on the option for SQL Server Management Studio.

    Note: I recommend that you pin this tool to your taskbar or start menu for easy access.

    SSMS

    If the search isn’t automatically pulled up use the ‘Run’ feature and type ‘SSMS’ and then click enter or OK. This should automatically launch SSMS if you have it installed.

    SSMS

    You should now be at the login screen for SSMS and see something similar to the below screenshot:

    SSMS


  1. For the Server Name field enter in the IP Address of the server you’re wanting to connect to, or the Fully Qualified Domain Name (FQDN) that points to the servers IP Address. If on your own dedicated server you can enter in the IP of the server you’re attempting to connect to. If on our shared servers you can use the hostname provided to you within your WCP control panel. You can find the database hostname next to the database in question under the ‘MS SQL’ icon in your control panel.

    Example: The below screenshot is an example of the server hostname for one of our shared servers. Please note that we have many shared SQL servers so you’ll want to make sure you grab the correct hostname and not assume based on the hostname provided in the screenshot.

    SSMS

    You can also view our tutorial topic on creating and managing databases and users via the WCP control panel if needed.

  2. For the Authentication Type choose the drop-down option for ‘SQL Server Authentication’.

  3. Now you will enter in the credentials (username and password) for the SQL user you’re wishing to connect to. Once done click the ‘Connect’ button or hit enter.

    You should now be connected. If you were NOT able to connect at all then we recommend making sure the credentials you’re using are correct and if needed reset / update the password for the database user.

SQL Server Management Studio FAQ


Question: I’m receiving a 'Login failed for user ‘’ error. What is the cause?

Answer: Please make sure you typed the username and password correctly. Try copy / pasting the password into an editor such as notepad to ensure no spaces are being used to connect. If needed you can reset your password for a user by following our tutorial topic here.


Question: I get past the login prompt but I’m not able to see my database. Why am I able to connect but not have permissions to view my database?

Answer: Microsoft SQL requires specific permissions to be able to view or make changes to a database. A lot of database instances, such as our shared servers for instance, do not allow normal users to view databases on a server unless they are set as the DBO (Database Owner). So you’ll want to make sure to set this user as the DBO correctly by setting the ‘Database Owner’ field for the database to the user you’re attempting to connect to. You can view our tutorial topic on updating the database settings, such as this DBO field, if needed.


Question: I’m receiving the following error when connecting, what is the cause?
A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Answer: This is most likely due to the IP Addresses you’re connecting from not being permitted to access SQL on the destination server. Our shared servers do not have this restriction, however if on your own dedicated server the firewall may be locked down. View our tutorial on opening up a port (such as SQL - 1433) on your VPS via the Firewall Management feature in WCP.