cert based login is not working using powershell

Question

Hi I'm new to this and want to perform cert based login in SQL server using powershell but its not working.

My flow is something like this:

My code is below and steps which i have followed:

In SQL server side

step1 :Replace dns with your system name
New-SelfSignedCertificate -CertStoreLocation Cert:\LocalMachine\My -DnsName test.corp.xyz.com -KeySpec KeyExchange -FriendlyName SQLCert

step2:search for mmc , then go to certificates you will find above created cert

step3:open sql server manager come to sql server network configuration section and right click on Protocols come to certificates tab and select above created certificate

step4:restart SQL server from sql server manager

step5:export certificate : Export-Certificate -Cert Cert:\LocalMachine\My\<Thumbprint value>-FilePath $PSScriptRoot\tstcert.cer

In User Side

step6:Check if certificate installed or not . replace thumbprint value
$present=Get-ChildItem -Path "Cert:\LocalMachine\My" | Where-Object Thumbprint -eq <Thumbprint value> | Select-Object *
if($present)
{}
else{
Copy-Item $PSScriptRoot\tstcert.cer -Destination C:\tstcert.cer
Import-Certificate -CertStoreLocation Cert:\LocalMachine\My -FilePath C:\tstcert.cer
}

step7:SQL connection
$SqlServer= 'server name'
$Database= 'Db Name'
$connString = "Data Source=$SqlServer;Database=$Database;Integrated Security=True;"

$conn = New-Object System.Data.SqlClient.SqlConnection $connString

$conn.Open()
if($conn.State -eq "Open")
{
Write-Host "Test connection successfull"
}

I want to make if this certificate is installed on system then user must sign in to SQL .
But its not working when different user is logged in , I might have missed out something or might be wrong at some point as I'm new to this.
Also what changes should i do in connection string to achieve requirement.

Also can anyone check if i have followed correct steps.

Any help will be really thankfull.

Answer 1

But its not working when different user is logged in

"Not working" means what in detail? Do the user get an error message and if, which one?

Answer 2

The certificate specified under protocols of SQL Server Configuration Manager is used for TLS encryption, not authentication. It is not required to installed this cert on the client since it is exchanged as part of the login process.

SQL Server uses Active Directory to authenticate users when Integrated Security=True is specified. Certificates are not used for authentication directly but as part of the token exchange between the client, AD, and SQL Server to as described in this article. Clients are authenticated using the AD credentials of the client process identity (e.g. logged in user). For integrated security authentication to succeed, the client's AD account needs to be granted access the SQL Server either directly or via AD group membership.

Based on the symptoms, it seems the first user's AD account has corresponding SQL login or is a member of an AD group with a login but the second user either does not have a login or doesn't have permissions to access the database specified in the connection string. The SQL Server error log will contain additional messages detailing the specific cause of the login error.

Answer 3

Hi @Shubham Kumar ,

The certificate authentication for SQL Server is equivalent to the Integrated Security feature, not the same as the certification you added
This article gives a detailed description:
https://blogs.msmvps.com/jcoehoorn/blog/2020/11/23/certificate-authentication-in-sql-server/

This kind of logins is where you can grant permissions to the certificate login, sign a procedure with the certificate and anyone who has execute permission on the procedure will have the additional permissions granted to the certificate login while the procedure is executing,

This article has instructions for certificate-based logins:
https://www.red-gate.com/simple-talk/devops/data-privacy-and-protection/introduction-to-sql-server-security-part-4/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hello there,

If you can share the exact error message when the login fails maybe we can track the reason for the script failure.

This error could occur when you try to make an encrypted connection to SQL Server using a non-verifiable certificate.

Change Integrated security to false in the connection string.

You can check/verify this by opening up the SQL management studio with the username/password you have and see if you can connect/open the database from there. It could be a firewall issue as well.

------------------------------------------------------------------------------------------------------------------------------------------

--If the reply is helpful, please Upvote and Accept it as an answer–