Connect to a remote SQL Server with SSMS

To connect SQL Server Management Studio to a hosted SQL Server, open Connect to Server and set four things: Server name is the host and port separated by a comma (db.example.net,14330 - a colon will not work), Authentication is SQL Server Authentication, Login and Password are the credentials you were given (often sa on a fresh server), and under encryption, leave it Mandatory and tick Trust server certificate unless the server has a certificate from a trusted authority. That covers nearly every first connection. The rest of this post explains each field, the options worth changing, what to do once you are in, and the exact error messages for each way it fails.

What you need before you start#

Four pieces of information and one program:

• Host - a hostname or IP address.
• Port - SQL Server's default is 1433, but hosted servers often use a different one because many servers share an address. Use the port you were given, not the default.
• Login - a SQL login name. On a new server this is usually sa, the built-in system administrator.
• Password - for that login.
• SSMS - SQL Server Management Studio, Microsoft's free management tool. It runs on Windows only.

Recent SSMS releases (version 20 and later) introduced the encryption options described below. If your copy is older than that, update it; old versions use an older driver with different defaults and miss years of fixes. On macOS or Linux, use Visual Studio Code with Microsoft's MSSQL extension, DBeaver, or the command-line sqlcmd - every field in this post has an equivalent there. Azure Data Studio, which used to be the cross-platform answer, has been retired by Microsoft, so do not start new work in it.

Before blaming SSMS, check that the port is reachable from your machine. In PowerShell:

PS> Test-NetConnection db.example.net -Port 14330

TcpTestSucceeded : True means the network path is open and anything that fails after that is a setting in SSMS or a credential. False means a firewall - yours, your network's, or the server's - is in the way, and nothing in SSMS will fix it. Some office and school networks block outbound traffic to unusual ports; try from another connection to rule that out.

The Connect to Server dialog, field by field#

The comma is the single most common mistake. SQL Server tools inherited the host,port syntax from the original client libraries, and everything in the Microsoft stack - SSMS, sqlcmd, ADO.NET connection strings, ODBC - uses it. Write db.example.net:14330 and the client treats the whole string as a host name, fails to resolve it or tries named pipes, and gives you a network error that says nothing about the colon. The backslash form, host\INSTANCE, is for named instances found through the SQL Server Browser service on UDP 1434, which hosted servers do not expose; with an explicit port you never need it.

Prefixing tcp: (tcp:db.example.net,14330) tells the client to use TCP and nothing else. It is optional, and it stops the client from trying other protocols first when something is misconfigured, which makes errors clearer.

On RE:NODE, SQL Server plans are set up so that this dialog is all you need: the server has its own sa password, and a database is created for you and set as sa's default, so SSMS opens straight into it. The server name is the host and port shown on the plan, with a comma.

Encryption and Trust server certificate#

SSMS 20 replaced the old "Encrypt connection" checkbox with an Encryption setting with three values, and moved to a driver that encrypts by default:

With Mandatory - the default - the client checks the server's certificate the way a browser checks a website's: it must chain to an authority the machine trusts, and its name must match the server name you typed. A SQL Server that has not been given a certificate generates a self-signed one at startup, which no machine trusts, so the connection fails with this:

A connection was successfully established with the server, but then an error occurredduring the login process. (provider: SSL Provider, error: 0 - The certificate chainwas issued by an authority that is not trusted.)

Ticking Trust server certificate keeps the connection encrypted and skips the validation. That is the normal setting for a hosted server with a self-signed certificate, and it is meaningfully better than turning encryption off: your password and data still cross the internet encrypted. What you give up is protection against someone intercepting the connection and presenting their own certificate, which requires them to sit on the network path between you and the server.

If the server has a certificate from a trusted authority for a name such as sql.example.com, connect using that name and leave Trust server certificate unticked to get full validation. Host name in certificate, under the connection properties, handles the case where you connect by one name and the certificate carries another.

Strict mode validates the certificate and is not for self-signed setups; with a server that presents a self-signed certificate, use Mandatory with Trust server certificate.

Options worth setting under Options#

The Options >> button opens three more tabs. The ones that matter for a remote server:

• Connect to database (Connection Properties) - <default> uses the login's default database. Typing a database name here opens straight into it. Setting it to master is the escape hatch for one particular error, described in troubleshooting below.
• Network protocol - <default> is fine; TCP/IP is what is used either way for a remote host.
• Connection time-out - raise it if you connect over a slow or distant link and see timeouts during login.
• Execution time-out - 0 means queries never time out from the client side, which is the SSMS default and what you want for long maintenance scripts.
• Use custom color - colours the status bar for this connection. Give production servers red. It costs nothing and has stopped many people running a test script in the wrong window.

SSMS can save connections with passwords in your Windows profile. Convenient on your own machine, a bad idea on a shared one, and the saved password for sa is the full key to the server.

First steps once you are connected#

Open a query window (Ctrl+N, or New Query) and confirm where you are:

SELECT @@SERVERNAME AS server_name,       SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('ProductVersion') AS version,       DB_NAME() AS current_database,       SUSER_SNAME() AS login_name;

On a hosted Express server this shows Express Edition (64-bit), a 16.0.x version number for SQL Server 2022, and the database you landed in. The database dropdown on the toolbar and USE [dbname]; both switch databases.

Then, before writing application code against sa, create a dedicated login and user for the application with only the permissions it needs. sa can drop every database on the server, and a connection string with it inside lives in config files, environment variables and developers' laptops. SQL Server logins, users and roles has the exact script. Once that exists, use sa for administration from SSMS and nothing else.

Object Explorer, on the left, shows databases, tables, views and security principals. Right-clicking a table gives Select Top 1000 Rows and Edit Top 200 Rows, which are fine for a look and poor for bulk changes - use T-SQL for anything that touches more than a handful of rows, inside a transaction you can roll back:

BEGIN TRANSACTION;UPDATE dbo.Customers SET Country = 'GB' WHERE Country = 'UK';-- check the row count in the Messages tab, then:COMMIT;   -- or ROLLBACK;

Moving data in and out with SSMS#

SSMS has several tools for this, and the right one depends on where the data is going:

• Generate Scripts (right-click the database, Tasks) writes schema and optionally data as T-SQL. Good for small databases and for moving between versions, since a script runs on any version that supports its syntax.
• Import Flat File loads a CSV into a new table with a preview of the column types. Quick for one-off imports.
• Export Data-tier Application produces a .bacpac, a schema-plus-data package that can be imported into another SQL Server or Azure SQL Database.
• Back Up and Restore works with native .bak files, but the file path in those dialogs is a path on the server, not on your PC. To restore a .bak you have locally, it has to be uploaded to the server first, to a directory the SQL Server process can read.

For anything large or repeatable, the command-line tools are better than the wizards: sqlcmd for scripts and bcp for bulk copying, covered in sqlcmd and bcp. Moving a whole database from another host is its own procedure, with version rules, in migrating a database to SQL Server hosting.

Troubleshooting connection errors#

`A network-related or instance-specific error occurred while establishing a connection to SQL Server. ... (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)` - the client never reached the server over TCP. Check for a colon instead of a comma, a wrong port, or a firewall. Test-NetConnection from above tells you which.

`(provider: TCP Provider, error: 0 - No such host is known.)` - the host name does not resolve. Typo, or a DNS record that does not exist yet.

`(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)` - you used host\INSTANCE, and the client is looking for the SQL Server Browser service. Use host,port instead.

`The certificate chain was issued by an authority that is not trusted.` - encryption is Mandatory and the certificate is self-signed. Tick Trust server certificate.

`The target principal name is incorrect.` - the certificate is valid but issued for a different name than the one you typed. Connect with the name on the certificate, set Host name in certificate, or trust the certificate.

`Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)` - wrong password, wrong login name, or the login is disabled. The message deliberately does not say which; the server's error log records a state number that does. Retype the password rather than pasting it, since pasted passwords often carry a trailing space.

`Cannot open user default database. Login failed. (Error: 4064)` - the login's default database was dropped, renamed, or is offline. This one catches people who tidy up: on a server where sa's default is the database created for you, dropping or renaming that database means sa can no longer sign in the normal way. Fix it by opening Options >> Connection Properties, typing master into Connect to database, connecting, and then running:

ALTER LOGIN [sa] WITH DEFAULT_DATABASE = [master];

Connection works, then drops after idle time. Something on the network path closes idle TCP connections. Reconnect, and keep long-running work in scripts rather than in idle windows.

FAQ#

Why does SSMS need a comma instead of a colon before the port?

Because SQL Server's client libraries define the server name as host,port and always have. The colon form, used by most other databases and URLs, is read as part of the host name. JDBC is the exception: its URLs use a colon.

Is it safe to tick Trust server certificate?

The connection stays encrypted, so passwords and data are not readable in transit. What it skips is proof of the server's identity, which protects against an attacker positioned on the network path. For a hosted server with a self-signed certificate, it is the standard setting; use a trusted certificate and full validation when the threat model calls for it.

Can I use SSMS on a Mac?

No, SSMS is Windows-only. Use Visual Studio Code with the MSSQL extension, DBeaver, or sqlcmd. The same server name, login and encryption settings apply in each.

Should I use the sa login for my application?

No. Use sa for administration from SSMS, and create a separate login for each application with only the permissions it needs. If the application's credentials leak, the damage is limited to what that login can do.

Why can I connect from home but not from work?

Your work network blocks outbound connections to the server's port. Run Test-NetConnection from both places to confirm, then either ask for the port to be allowed or connect from elsewhere.