cert based login is not working using powershell

Shubham Kumar 1 Reputation point

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 *
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

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,581 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,173 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Olaf Helper 36,086 Reputation points

    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?

  2. Dan Guzman 8,516 Reputation points

    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.

    0 comments No comments

  3. YufeiShao-msft 7,031 Reputation points

    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:

    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:


    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.

    0 comments No comments

  4. Limitless Technology 43,226 Reputation points

    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–

    0 comments No comments