Connection String

Author : MD TAREQ HASSAN | Updated : 2021/09/26

Understanding Connection String

Connection string format

Data Source=<server-name>\<instance-name>;Database=<database-name>;Integrated Security=<True/False>;User ID=<DB-user-name>;Password=<DB-user-password>;

Server=<server-name>\<instance-name>;Database=<database-name>;Integrated Security=<True/False>;User ID=<DB-user-name>;Password=<DB-user-password>;

Data Soucre

Data Source=<server-name>\<instance-name>
Alias (different alias require different values)
- Server
- Address
- Addr
- Network Address
Server name:
- you can use any of the followings to as server name
  - ”(local)”: just means means to use the SQL Server installed in the current machine
  - ”.”: same as local
  - “xyz”: string literal for the server name i.e. if your PC name is XYZPC, then server name might be ‘XYZPC’
- If a server name is not specified, a connection will be attempted to the default instance on the local computer
Instance name:
- Must set explicitly during installation (default for ‘instance name’: no name)
- For no name instance, ‘Server=<server-name>\<instance-name>’ will not work. See Important Points About Instance Name
- MSSQLSERVER, SQLEXPRESS are not instance name, these are just SQL Server Service name
- First instance installed on the server/host is the default instance (default instance can have name)
  - You installed SQL Server Developer Edition twice
  - “Instance_1” and “Indicates_2”
  - “Instance_1” is installed first -> default instance
Example:
- Server=(local)
- Data Source=(local)\FooInstance

Database

Alias: “Initial Catalog”
Indicates the database we want to first connect
Example:
- Initial Catalog=AdventureWorksLT2019
- Initial Catalog=TestDatabase

Integrated Security

Alias: “Trusted_Connection”
Default value: false
Other values:
- Integrated Security=true: means Windows authentication will be used
- Integrated Security=SSPI: same as true
- Integrated Security=false: means id and password will be provided in the connection string

See full list of supported properties: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?#remarks

Construct Connection String

Connection string for Developer Edition running locally

Server Name: “HOVER”
Instance Name: no name (default)
Target Database: “AdventureWorksLT2019”
User Id: “FooUser”
Pasword: “FooUser.2021”

When specifying a local server with no name (default) instance, always use ‘(local)’

Data Source=(local);Database=AdventureWorksLT2019;Integrated Security=false;User ID=FooUser;Password=FooUser.2021;

Connection string for Developer running in TestServer with IP address 10.1.1.10

Data Source=10.1.1.10\FooInstance;Database=AdventureWorksLT2019;Integrated Security=false;User ID=FooUser;Password=FooUser.2021;

Connection String for LocalDB

Localdb is a minified version of SQL Express that require less number of pre-req to install and offers a fast, zero-configuration installation.
Details about LocalDB:
- https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb
- https://docs.microsoft.com/en-us/archive/blogs/jerrynixon/sql-express-v-localdb-v-sql-compact-edition#at-a-very-high-level-localdb-has-the-following-key-properties

Server=(localdb)\MSSQLLocalDB;Integrated Security=true

# To connect to a specific database by using the file name, connect using a connection string
Server=(localdb)\MSSQLLocalDB;Integrated Security=true;AttachDbFileName=D:\Data\MyDB1.mdf

Visual Studio

LocalDB will is installed with Visual Studio
Visual Studio can start and stop LocalDB
If your application uses local DB
- Visual Studio will start LocalDB process when debugging is started
- Visual Studio will stop LocalDB process when debugging is done
- All data will be lost
Use “SQL Server Object Explorer” to connect to LocalDB
ProjectsV13 DB: SQL Server Data Tools uses a private LocalDB instance called “ProjectsV13”

Connect to LocalDB using SSMS

Open SSMS
Server Name: (localdb)\MSSQLLocalDB
Use windows authentication

Tools for Generating Connection String

Online tools