The most common SQL Server Connection for development environments in .NET are
Data Source=localhost; Database=your_test_db; Trusted_Connection=True. Notice the
Trusted_Connection is set to
True and a username and password is not supplied. This is because it uses Windows Authentication.
At times this is not enough, and we need a SQL Server login. This is generally true for other environments such as test and production.
Below I breakdown the various SQL commands in order to create a SQL server login and grant necessary access.
In order to create a SQL Server login for a particular database, it can be broken down into three areas which I will cover below.
- Create a SQL Server login
- Create a SQL User associated with the login created for a specific database
- Grant necessary permissions for the SQL Server user such as readonly.
TLDR; You can jump to the final SQL Screipt to find the SQL script.
1. Create a SQL Server Login
First, we need to create a login. A login in SQL Server only allows access to the server.
In the example below, we are going to create a login for
local_dev_user with password
USE [master] CREATE LOGIN local_dev_user WITH PASSWORD = 'azNSiLKN1VnFQDuC'; -- set your own pwd here
Notice, the use of the
master database statement in the first line. This is required so that a login is created against the master database.
2. Create a user for a specific database
Next, we need to create a user associated to the login we created in step 1 and also for a particular database.
USE [your_test_db] -- Set your database here -- Creates a database user for the login created above. CREATE USER local_dev_user FOR LOGIN local_dev_user GO
Here, notice that we have set the desired database that the login is needed with the first
3. Grant permissions
Now, we can grant permissions and add the relevant roles for the user.
EXEC sp_addrolemember 'db_datareader', 'local_dev_user'; EXEC sp_addrolemember 'db_datawriter', 'local_dev_user'; EXEC sp_addrolemember 'db_owner', 'local_dev_user';
Here, I am setting the user to be a reader, writer and an owner.
The final SQL Script
Here is the final SQL script for reference.
USE [master] CREATE LOGIN local_dev_user WITH PASSWORD = 'azNSiLKN1VnFQDuC'; -- set your own pwd here USE [your_test_db] -- Set your database here -- Creates a database user for the login created above. CREATE USER local_dev_user FOR LOGIN local_dev_user GO EXEC sp_addrolemember 'db_datareader', 'local_dev_user'; EXEC sp_addrolemember 'db_datawriter', 'local_dev_user'; EXEC sp_addrolemember 'db_owner', 'local_dev_user';
The SQL Server connection string
Finally, the SQL connection string for a .NET app now needs to be modified.
"Data Source=localhost; Database=your_test_db; User ID=local_dev_user;Password=azNSiLKN1VnFQDuC; Encrypt=true; TrustServerCertificate=True"
Also, be sure to check the SQL Server authentication mode is set to both "SQL Server and Windows Authentication mode". This can be done by right-clicking on the server in SQL Management Studio and selecting "Properties". In the Server Properties dialog, under "Security", you can check the authentication mode enabled: