SQL Server SSL 

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

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.

True, then you need to take the step of making sure extra setup is done and that can be annoying to do.

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

i have this also, even i follow the same steps that show in the video. Am i missed out any?

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've posted it to the video description along with a link to the TechNet article with all the options.

what was the command ? and what was the OS

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

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.