How to create a certificate and symmetric key logins to encrypt a database connection?

Problem: How to create a certificate and symmetric key logins to encrypt a database connection?

Solution: U​SE​R

For giving access to a user to SQL Server Instance we must create a login for the user or user group in the master database and we can authenticate the login.

      1.     Certificate
You can create a certificate to be associated with a login if a database master key is present
by using the CREATE CERTIFICATE statement
2.     Asymmetric Key
You can also use an asymmetric key to authenticate SQL logins. Unlike certificates, asymmetric
keys contain both a public key and a private key.

Example 1:

In this example, I would like to show we can create a certificate and create login using the certificate with the help of T-SQL.

Use master

GO

--creating master key.

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Encrytpion123!@#'

Use master

GO

--creating certificate

CREATE CERTIFICATE TDE_CERTIFICATE WITH SUBJECT ='TDE CERTIFICATE';

Use master

GO

--creating login from certificate

CREATE LOGIN TDE_CERTIFICATE FROM CERTIFICATE TDE_CERTIFICATE;

Example 2:

In this example, I would like to show we can create a master key and create login using the certificate with the help of T-SQL.

Use master

GO

--creating master key.

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Encrytpion456!@#'

USE master

GO

CREATE ASYMMETRIC KEY Sql_user WITH ALGORITHM =RSA_2048

USE Master

GO

IF NOT EXISTS(

select * from sys.asymmetric_keys where name='Sql_user')

BEGIN

CREATE LOGIN sql_user FROM ASYMMETRIC KEY Sql_user;

END

  

To verify a login whether login is created we can verify it by going to the security node and clicking the logins folder or checking in the System catalog views for server credentials/ logins.

select name,type_desc from master.sys.server_principals

where name 

IN(

'sql_user',

'TDE_CERTIFICATE')

OUTPUT:

name	type_desc
sql_user	ASYMMETRIC_KEY_MAPPED_LOGIN
TDE_CERTIFICATE	CERTIFICATE_MAPPED_LOGIN

In the later articles let’s see how to use certificate and asymmetric keys for logins.