Hi all this is the current version I am using on SQL Servers, $params = @{ DnsName = "$env:COMPUTERNAME","$env:COMPUTERNAME.lab.net" KeyLength = 2048 KeyFriendlyName = "SQLServerCert" KeyAlgorithm = 'RSA' HashAlgorithm = 'SHA256' KeyExportPolicy = 'Exportable' KeySpec = 'KeyExchange' NotAfter = (Get-date).AddYears(2) Provider = 'Microsoft RSA SChannel Cryptographic Provider' CertStoreLocation = 'Cert:\LocalMachine\My' } $Cert = New-SelfSignedCertificate @params
Thank you for the video. I tried the script but it returned with the following error: New-SelfSignedCertificate : A parameter cannot be found that matches parameter name 'KeyAlgorithm'. At line:13 char:35 It's Windows Server 2012 R2 with PowerShell version is 5.1.14409.1018. I've very limited experience with PowerShell so maybe I did something wrong? Any advice? Thanks again.
New-SelfSignedCertificate : A parameter cannot be found that matches parameter name 'KeySpec'. Windows PowerShell ----> 2014 Microsoft Corporation Yours is ------> 2016 How can I Make it upgrade it 2016? Secondly How to check what version of powerShell I am running?
Hi Azhar, I'm using windows 2016 that comes with Powershell 5 by default if you have Windows 2008 or Windows 7 and higher you can just download Powershell 5 from Microsoft. by downloading Windows Management Framework 5.1 As for the version you can check this by opening a PowerShell terminal and typing $PSVersionTable Hope that helps.
You don't need to change the service account to local system if you already gave read permission or full control to the service running the engine on manage private key section of the certificate using MMC.
Host name normally and if you want to have it public then browser support comes with some overhead. www.thesslstore.com/blog/how-to-become-a-certificate-authority/
I got this error, could you please advise what to do? thank you New-SelfsignedCertificate : CertEnroll::CX509Enrollment::_CreateRequest: Access is denied. 0x80070005 (WIN32: 5 ERROR_ACCESS_DENIED)
the last 4 version of SQL Drivers support Always on and version before that support SSL so this should be a problem, however depending on the connection string type for JDBC String connectionUrl = "jdbc:sqlserver://localhost:1433;" + "databaseName=AdventureWorks;integratedSecurity=true;" + "encrypt=true;trustServerCertificate=true"; in ASP.net "Server=localhost,1433;Database=dbname;uid=username;pwd=password;encrypt=true" These are fairly easy to find on google.
Sir, i need to know how do i generate a self signed certificate using sha 256 hashing algorithm with minimum 2048 bits key and use that certificate in SQL server ssl encryption? and also can i use this self signed certificate on production environment also?
You can used the KeyAlgorithm and KeyLength switches to do this. This is not recommended for production ,however, if you have an automated way to renew the certificate and the public key of the certificate is imported to the trusted store then technically there is nothing wrong but I would recommend using a PKI if possible. docs.microsoft.com/en-us/powershell/module/pki/new-selfsignedcertificate?view=windowsserver2019-ps
Good question. basically there are four steps, 1) get new certificate, 2) install certificate, 3) replace the thumbprint to the new one and restart the service. 4) if step 3 went well remove the old certificate.
I tried the same step, but though it creates the certificate, it doesnt appear in the drop downlist of certificate for "Protocol for MSSQLServer Properties". Please help, as I tried in two different instance, and didnt work in either. My server is SQL server 2014 and 2016
Right here are some tips and things to check. Certificate must be present in the Local computer certificate store or the current user certificate store. Certificate age must be present within the validity period. Certificate must be meant for server authentication. (EKU should specify Server Authentication [1.3.6.1.5.5.7.3.1]) Certificate must be created using the KEY_SPEC option of AT_KEYEXCHANGE (KEY_SPEC=1) Common name of the certificate should be the host name or the FQDN of the server computer. Certificate should use KeyAlgorithm RSA sql server has problems supporting other kinds. if you still have a problem please send me the command you are using.
@@yasminelakrib366 Any powershell session on the server should work just fine, I'm using PowerShell 5.1 the default with windows 2016 and 2019 and also tested with 7.2 powershell as well.
Blocked files can be fixed by right clicking on the file and changing the blocked under general tab. Or you can use the powershell cmdlet to change the file settings. docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/unblock-file?view=powershell-7
Hi all this is the current version I am using on SQL Servers,
$params = @{
DnsName = "$env:COMPUTERNAME","$env:COMPUTERNAME.lab.net"
KeyLength = 2048
KeyFriendlyName = "SQLServerCert"
KeyAlgorithm = 'RSA'
HashAlgorithm = 'SHA256'
KeyExportPolicy = 'Exportable'
KeySpec = 'KeyExchange'
NotAfter = (Get-date).AddYears(2)
Provider = 'Microsoft RSA SChannel Cryptographic Provider'
CertStoreLocation = 'Cert:\LocalMachine\My'
}
$Cert = New-SelfSignedCertificate @params
Thank you for the video. I tried the script but it returned with the following error: New-SelfSignedCertificate : A parameter cannot be found that matches parameter name 'KeyAlgorithm'.
At line:13 char:35 It's Windows Server 2012 R2 with PowerShell version is 5.1.14409.1018. I've very limited experience with PowerShell so maybe I did something wrong? Any advice? Thanks again.
Not near my lab right now but once I am will give it a try on windows 2012 image
Thanks you for the quality content 👍 just what I needed
I am getting ssl security error when connecting odbc to SQL server.. Could you please help? Tls1.0 already enabled on sql server.
SQL Certificate is generated but in CM Window, it is not showing in Certificate Tab under Network configuration. Please help.
Resolveu meu problema aqui, obrigado!
well i still get an error "The certificate chain was issued by an authority that is not trusted"
i have this also, even i follow the same steps that show in the video. Am i missed out any?
New-SelfSignedCertificate : A parameter cannot be found that matches parameter name 'KeySpec'.
Windows PowerShell
----> 2014 Microsoft Corporation
Yours is ------> 2016 How can I Make it upgrade it 2016?
Secondly How to check what version of powerShell I am running?
Hi Azhar,
I'm using windows 2016 that comes with Powershell 5 by default if you have Windows 2008 or Windows 7 and higher you can just download Powershell 5 from Microsoft. by downloading Windows Management Framework 5.1
As for the version you can check this by opening a PowerShell terminal and typing $PSVersionTable
Hope that helps.
Thank you so much! Very useful video!
You don't need to change the service account to local system if you already gave read permission or full control to the service running the engine on manage private key section of the certificate using MMC.
True, then you need to take the step of making sure extra setup is done and that can be annoying to do.
If I want to use a certificate for public ip?
DnsName = "$ipPublic" ??? or name host?
Host name normally and if you want to have it public then browser support comes with some overhead.
www.thesslstore.com/blog/how-to-become-a-certificate-authority/
I got this error, could you please advise what to do? thank you New-SelfsignedCertificate : CertEnroll::CX509Enrollment::_CreateRequest: Access is denied. 0x80070005 (WIN32: 5
ERROR_ACCESS_DENIED)
Beautiful demo!
Very useful video! Do I have to change something to my connection strings and what drivers are supported?
the last 4 version of SQL Drivers support Always on and version before that support SSL so this should be a problem, however depending on the connection string type for JDBC
String connectionUrl =
"jdbc:sqlserver://localhost:1433;" +
"databaseName=AdventureWorks;integratedSecurity=true;" +
"encrypt=true;trustServerCertificate=true";
in ASP.net
"Server=localhost,1433;Database=dbname;uid=username;pwd=password;encrypt=true"
These are fairly easy to find on google.
is ti me or the audio is very low?
Awesome video
good job.thanks
Sir, i need to know how do i generate a self signed certificate using sha 256 hashing algorithm with minimum 2048 bits key and use that certificate in SQL server ssl encryption? and also can i use this self signed certificate on production environment also?
You can used the KeyAlgorithm and KeyLength switches to do this.
This is not recommended for production ,however, if you have an automated way to renew the certificate and the public key of the certificate is imported to the trusted store then technically there is nothing wrong but I would recommend using a PKI if possible.
docs.microsoft.com/en-us/powershell/module/pki/new-selfsignedcertificate?view=windowsserver2019-ps
Can you explain how to renew the certificat if expire ?
Good question.
basically there are four steps, 1) get new certificate, 2) install certificate, 3) replace the thumbprint to the new one and restart the service. 4) if step 3 went well remove the old certificate.
Muy buen video..
Una pregunta como importo mi certificado en sql para encriptar mis datos?
Es una tarea para la uni
I tried the same step, but though it creates the certificate, it doesnt appear in the drop downlist of certificate for "Protocol for MSSQLServer Properties". Please help, as I tried in two different instance, and didnt work in either. My server is SQL server 2014 and 2016
Right here are some tips and things to check.
Certificate must be present in the Local computer certificate store or the current user certificate store.
Certificate age must be present within the validity period.
Certificate must be meant for server authentication. (EKU should specify Server Authentication [1.3.6.1.5.5.7.3.1])
Certificate must be created using the KEY_SPEC option of AT_KEYEXCHANGE (KEY_SPEC=1)
Common name of the certificate should be the host name or the FQDN of the server computer.
Certificate should use KeyAlgorithm RSA sql server has problems supporting other kinds.
if you still have a problem please send me the command you are using.
@@TipsForITPros Same problem here... I follow step by step and certificate does´t appear on dropdown in SQL Server Configuration Manager...
@@leonardoyanesbatista1334
OK here is an example, please keep in mind that values you might want to change, like domain and valid till.
$params = @{
DnsName = "$env:COMPUTERNAME","$env:COMPUTERNAME.lab.net"
KeyLength = 2048
KeyFriendlyName = "SQLServerCert"
KeyAlgorithm = 'RSA'
HashAlgorithm = 'SHA256'
KeyExportPolicy = 'Exportable'
KeySpec = 'KeyExchange'
NotAfter = (Get-date).AddYears(2)
Provider = 'Microsoft RSA SChannel Cryptographic Provider'
CertStoreLocation = 'Cert:\LocalMachine\My'
}
$Cert = New-SelfSignedCertificate @params
@@TipsForITPros Please I really need ur help, where can i use this command ???
@@yasminelakrib366 Any powershell session on the server should work just fine, I'm using PowerShell 5.1 the default with windows 2016 and 2019 and also tested with 7.2 powershell as well.
Could you post the script you used in your Powershell to creat the certificate? Thanks!
I've posted it to the video description along with a link to the TechNet article with all the options.
Ty guy !
Hii im facing the error it show setup block kindly hlp me to resolv this error
What's the error and command you are running
@@TipsForITPros when im instal sql serwer managemnt tool after downloding this when i run this it show setup blok error
Blocked files can be fixed by right clicking on the file and changing the blocked under general tab.
Or you can use the powershell cmdlet to change the file settings.
docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/unblock-file?view=powershell-7
then we click on thee stiffkit🤣