Connection string syntax with an example

What is a connection string?

In programming, a connection string keeps initialisation settings required by the data provider to connect to a database. A connection string is built of key/value pairs, holding information like the server name, database name, authentication method and encryption settings.

I have a local installation of SQL Server Express 2022 and this is the connection string I am using to connect with it.

Detailed look of a connection string

1. Server=[ServerName\\InstanceName]

   This is the name of my server followed by the name of the server instance. In MSSQL a server can be configured to host multiple instances. Each instance will have its unique name. By default when you install sql server express it will come with one instance named SQLEXPRESS. To refer to this instance you can use one of the following:

   • computer name\\SQLEXPRESS

     Server=AnnaPC\\SQLEXPRESS; Initial Catalog=SuperMarket_Dev; Integrated Security=True; TrustServerCertificate=True;

   • localhost\\SQLEXPRESS

     Server=localhost\\SQLEXPRESS; Initial Catalog=SuperMarket_Dev; Integrated Security=True; TrustServerCertificate=True;

   • .\\SQLEXPRESS (same as localhost\\SQLEXPRESS)

     Server=.\\SQLEXPRESS; Initial Catalog=SuperMarket_Dev; Integrated Security=True; TrustServerCertificate=True;

   • Server's public IP\\SQLEXPRESS

     Server=45.12.xxx.xxx\\SQLEXPRESS; Initial Catalog=SuperMarket_Dev; Integrated Security=True; TrustServerCertificate=True;

   • Server's DNS name\\SQLEXPRESS

     Server=CompanyServer\\SQLEXPRESS; Initial Catalog=SuperMarket_Dev; Integrated Security=True; TrustServerCertificate=True;

   Based on my connection string I am using a sql server installed locally and its instance is called SQLEXPRESS

   Server=localhost\\SQLEXPRESS;

2. Initial Catalog=[DatabaseName] or Database=[DatabaseName]

   This key value pair holds information about the database name. Both [Database] and [Initial Catalog] are equivalent and will both work the same way. My database is called SuperMarket_Dev.

   Initial Catalog=SuperMarket_Dev;

3. Integrated Security=[True|False|SSPI] or Trusted_Connection=[True|False|SSPI]

   Integrated security marks if windows authentication will be used or not. Set this property to True if you want to use your windows credentials to log into the server. Keep in mind this setting will only work if the server is also configured to accept Windows Authentication. SSPI is equivalent to True for MSSQL. It is primarily used as a True value in Oracle databases. Both [Integrated Security] and [Trusted_Connection] are equivalent and will both work the same way. In my connection string it is set to true.

   Integrated Security=True;

4. TrustServerCertificate=[Boolean];

   By default, SQL Server supports SSL/TLS encryption for communication. SQL Server uses a certificate to encrypt the data transmitted between the server and its client. If the encryption is switched on on the server each client must use encrypted connection.

   SQL server works with two types of certificates:

   • Certificates issued by globally trusted Certificate Authority (CA). These certificates are trusted by the client by default.
   • Self-signed certificates. These certificates are issued by the server itself and are not trusted by default by the client. In order for the client to trust such certificates you need to set TrustServerCertificate to True.

   Initial Catalog=SuperMarket_Dev;

5. User ID=[Username]

   This key-value pair specifies the username used to authenticate with the database. It is required when using SQL Server Authentication or other database-specific authentication methods. For example, if the username is "AdminUser," the connection string would look like: User ID=AdminUser;

   If your server supports Windows Authentication (Integrated Security), you do not need to include the User ID key, as your Windows credentials will be used instead.

6. Password=[Password]

   This key-value pair specifies the password associated with the username. Ensure the password matches the credentials for the specified username to successfully connect to the database. For security reasons, avoid including plain-text passwords in your connection strings. Instead, consider using environment variables or secure storage solutions.

   One way to keep your passwords secure is by keeping the connection string in appsettings file.

7. AttachDbFilename=[.mdf file location]

   The AttachDbFilename key specifies the path to a local database file (typically .mdf) that the application should dynamically attach at runtime. This option is commonly used with SQL Server Express to attach a database file without requiring it to be pre-attached.

   Once attached, the database will remain attached to the SQL Server instance even after the connection is closed. Any subsequent requests to the database will use the already attached instance, and the database will not be reattached unless explicitly detached.

   AttachDbFilename=C:\Databases\MyDatabase.mdf;

   If AttachDbFilename is not specified in the connection string, and there is already an attached database that matches the Data Source key, that database will be used as the default database for the connection.

8. Connection Timeout=[seconds];

   The Connection Timeout specifies the maximum amount of time (in seconds) that the application will wait to establish a connection to the database before an error is thrown. If the connection cannot be made within the specified time, the application will stop trying and return a timeout error. This setting is useful for handling situations where the database might be unreachable due to network issues or server unresponsiveness.