Edit

Logging In to SQL Server

  • Article

Applies to: SQL Server

You can log in to an instance of Microsoft SQL Server by using any of the graphical administration tools or from a command prompt.

When you log in to an instance of SQL Server by using a graphical administration tool such as SQL Server Management Studio, you are prompted to supply the server name, a SQL Server login, and a password, if necessary. If you log in to SQL Server using Windows Authentication, you do not have to provide a SQL Server login each time you access an instance of SQL Server. Instead, SQL Server uses your Microsoft Windows account to log you in automatically. If SQL Server is running in mixed mode authentication ( SQL Server and Windows Authentication Mode), and you choose to log in using SQL Server Authentication, you must provide a SQL Server login and password. When possible, use Windows Authentication.

Note

If you selected a case-sensitive collation when you installed SQL Server, your SQL Server login is also case sensitive.

Format for Specifying the Name of SQL Server

When connecting to an instance of the Database Engine, you must specify the name of the instance of SQL Server. If the instance of SQL Server is the default instance (an unnamed instance), then specify the name of the computer where SQL Server is installed, or the IP address of the computer. If the instance of SQL Server is a named instance (such as SQLEXPRESS), then specify the name of the computer where SQL Server is installed, or the IP address of the computer, and add a slash and the instance name.

The following examples connect to an instance of SQL Server running on a computer named APPHOST. When specifying a named instance, the examples use an instance name SQLEXPRESS.

Examples:

Type of Instance Entry for the server name
Connection to a default instance using the default protocol. APPHOST
Connection to a named instance using the default protocol. APPHOST\SQLEXPRESS
Connection to a default instance on the same computer using a period to indicate that the instance is running on the local computer. .
Connection to a named instance on the same computer using a period to indicate that the instance is running on the local computer. .\SQLEXPRESS
Connection to a default instance on the same computer using localhost to indicate that the instance is running on the local computer. localhost
Connection to a named instance on the same computer using localhost to indicate that the instance is running on the local computer. localhost\SQLEXPRESS
Connection to a default instance on the same computer using (local) to indicate that the instance is running on the local computer. (local)
Connection to a named instance on the same computer using (local) to indicate that the instance is running on the local computer. (local)\SQLEXPRESS
Connection to a default instance on the same computer forcing a shared memory connection. lpc:APPHOST
Connection to a named instance on the same computer forcing a shared memory connection. lpc:APPHOST\SQLEXPRESS
Connection to a default instance listening on TCP address 192.168.17.28 using an IP address. 192.168.17.28
Connection to a named instance listening on TCP address 192.168.17.28 using an IP address. 192.168.17.28\SQLEXPRESS
Connection to a default instance that is not listening on the default TCP port, by specifying the port that is being used, in this case 2828. (Specifying a port number is not necessary if the Database Engine is listening on the default port (1433).) APPHOST,2828
Connection to a named instance on a designated TCP port, in this case 2828. (Specifying a port number is often necessary if the SQL Server Browser service is not running on the host computer.) APPHOST,2828
Connection to a default instance that is not listening on the default TCP port, by specifying both the IP address and the TCP port that is being used, in this case 2828. 192.168.17.28,2828
Connection to a named instance by specifying both the IP address and the TCP port that is being used, in this case 2828. 192.168.17.28\SQLEXPRESS,2828
Connecting to default instance by name, forcing a TCP connection. tcp:APPHOST
Connecting to named instance by name, forcing a TCP connection. tcp:APPHOST\SQLEXPRESS
Connecting to a default instance by specifying a named pipe name. \\APPHOST\pipe\SQL\query
Connecting to a named instance by specifying a named pipe name. \\APPHOST\pipe\MSSQL$SQLEXPRESS\SQL\query
Connecting to default instance by name, forcing a named pipes connection. np:APPHOST
Connecting to named instance by name, forcing a named pipes connection. np:APPHOST\SQLEXPRESS

Verifying your Connection Protocol

When connected to the Database Engine, the following query will return the protocol used for the current connection, along with the authentication method (NTLM or Kerberos), and will indicate if the connection is encrypted.

SELECT net_transport, auth_scheme, encrypt_option   
FROM sys.dm_exec_connections   
WHERE session_id = @@SPID;

Log In to an Instance of SQL Server (Command Prompt)

The following resources can help you troubleshoot a connection problem.

Related Content