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.