sor41ng.netlify.app

-->

A few days back, I was working with one of our partners who had a requirement of creating a SSL self-signed certificate through MMC console. As we are already aware that it is a complex and a tedious procedure, tried developing a script to ease the task for us. Also found that there were a lot of partners asking for assistance in having a script based approach to create the certificates.
Tried finding a way out by looking through various discussion forums which yielded nothing, but queries to build a script to accomplish the task. Addressing this requirement of the partner pool, here is the blog explaining the script based way of creating the Self-signed certificates and registering them meeting the pre-requisites of SQL server.
By developing the script based way of creating the certs, it is just at the run of a command we will get the SSL self-signed certificates created and ready to be registered. Along with the creation of the certificate, this blog also explains the different ways of registering those certificates.

Scenario 1:

• Encryption Keys (SSRS Native Mode); 4 minutes to read +1; In this article. Use the Encryption Keys page to manage the symmetric key that is used to encrypt and decrypt data in a report server. Managing the encryption keys is an important part of report server configuration.
• Generate a new key (.pfx) Enter a password, and then re-enter the password to confirm. Select Create, and then select the created file notification on your browser. The encryption key.PFX file is downloaded to your web browser's default download folder.
• The encrypted values are stored both in the Reporting Services configuration files and in the Report Server database. In the event you restore a SSRS database to a new server, the encryption keys will need to be loaded onto the new server in order to allow that server to read and utilize all of the items noted in the above list.

If a database encryption key has been modified twice, a log backup must be performed before the database encryption key can be modified again. In our tip, it is the first time we are modifying the encryption key, so the command below will work fine to bind the DEK to the certificate. SSRS 2008: Unable to restore encryption key. Ask Question Asked 9 years, 6 months ago. I have copied Reportserver and reportservertempdb database across and configured SQl server Reporting services 2008 to use this new database. I am not able to restore SSRS key over to new SSRS 2008 server. The key restore reports. Restore encryption keys -Reporting Services Configuration Manager (Native Mode) Start the Reporting Services Configuration Manager, and then connect to the report server instance you want to configure. On the Encryption Keys page, select Restore. Select the.snk file that contains the back up copy. Type the password that unlocks the file.

I will be creating a SSL self-signed certificate using the following 3 methods:

• Using Makecert util from the SDK.
• Using certreq command and a script.sine
• Using powershell command.

Steps to be followed:

1. Using Makecert util:

Ssrs Generate A New Encryption Key Software

• Firstly, the pre-requisite for using this method is to have Windows SDK installed on the machine.
• Navigate to the location where you have the makecert util and then Run the below command from elevated CMD prompt:
• Run the following command to create the certificate:

makecert -r -pe -n 'CN=MININT-Q99PLQN.fareast.corp.microsoft.com' -b 10/16/2015 -e 12/01/2020 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp 'Microsoft RSA SChannel Cryptographic Provider' -sy 12

• We will have the certificate created under the MMC console --> Certificate snap in --> Local Computer --> Personal section
• As per the parameters specified, the certificate will be created with the following set of specifications:
  • The common name of the certificate will be “MININT-Q99PLQN.fareast.corp.microsoft.com” which is the FQDN of the machine.
  • The private key will be enabled for exporting.
  • Certificate will be created in the Computer account -> Personal -> Certificate store
  • Validity period will be 10-16-2015 to 12-01-2020
  • The server authentication will be enabled. [eku = 1.3.6.1.5.5.7.3.1]
  • Key Spec value will be set to 1. [AT_KEYEXCHANGE will be enabled]
  • The algorithm used here for encryption is Microsoft RSA SChannel Cryptographic Provider.

ireb r7 for mac download2.Using Certreq command:

• Firstly, we need to save the below script in a text document with a .inf extension.

[Version] Signature = '$Windows NT$' [NewRequest] Subject = 'CN = MININT-Q99PLQN.fareast.corp.microsoft.com' FriendlyName = test1.contoso.com MachineKeySet = true RequestType=Cert ;SignatureAlgorithm = SHA256 KeyLength = 4096 KeySpec = 1 KeyUsage = 0xA0 MachineKeySet = True Exportable = TRUE Hashalgorithm = sha512 ValidityPeriod = Years ValidityPeriodUnits = 10 [EnhancedKeyUsageExtension] OID=1.3.6.1.5.5.7.3.1

• Navigate to the location where you have saved this request.inf file and then Run the below command from elevated CMD prompt

Certreq -new -f .inf .cer

• We will have the certificate created under the MMC console --> Certificate snap in --> LocalComputer --> Personal section
• The advantages of this technique is that it does not require the Windows SDK installed and the key length can be subjected to changes where as if it is using makecert it would be by defaultset to '2048' for 'RSA' and '512' for 'DSS'

3.Using Power-shell command

• Here is the approach to create the SSL certificate satisfying the pre-requisites to load it for SQL server using the power-shell command.
• Run Powershell as an administrator and enter the following command (where DnsName = Host name or FQDN of the machine)

New-SelfSignedCertificate -DnsName MININT-Q99PLQN.fareast.corp.microsoft.com -CertStoreLocation cert:LocalMachineMy -FriendlyName test99 -KeySpec KeyExchange

Scenario: 2

I will be registering the SSL self-signed certificate using the following 2 methods:

• Through the SQL Server Configuration Manager
• Through explicit registration

Steps to be followed:

1. Through SQL Server Configuration Manager:

• Initially need to check the health of the certificate using the CheckSQLssl.exe tool.
• Here are the pre-requisites for the SSL certificate to use it for SQL server:
  • 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.
  • Running the tool using the command prompt will generate the following report

• On getting all the validation checks ‘OK’ regarding the pre-requisites of the certificate we can go ahead register it.
• On SSCM, expand SQL server network configuration -> Right click on ‘Protocols for <Instance name> -> Properties. Turn the ‘Forced Encryption’ to Yes.

• Click on the ‘Certificate’ tab where the certificates will be listed and select the required certificate from the list and restart the service.

• Thus the SSL certificate will be loaded to the selected SQL server and this can be verified by analyzing the SQL error logs for the below message and verifying it with the thumbprint of the certificate in MMC.

The certificate [Cert Hash(sha1) 'BFB714872C7B2CD761ADEB1893BFC99581D3420B'] was successfully loaded for encryption.

• To verify the thumbprint, in MMC double click on the certificate which is loaded, click on ‘Details’ tab and click on thumbprint in the list.

2.Through explicit registration:

• Even after the validation checks are proved to be OK by the CheckSQLssl tool and still if the certificate is not listed in SSCM, then follow this technique.
• Run ‘regedit’ and open HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLServerSuperSocketNetLib and enter the thumbprint of the certificate without spaces to the ‘Certificate’ value.
• Note that in case of a clustered environment in those nodes whose FQDN does not match with the certificate name, the certificate will not be listed in the configuration manager. In that case explicit registration is the only way to register the certificate.
• Then on restarting the SQL service the SSL certificate will be loaded to SQL and this can be verified again by analyzing the SQL server error logs.

Written by:
Shreyas R, Support Engineer, SQL Server Support

Reviewed by:
Sravani Saluru, Support Escalation Engineer, SQL Server Support
Pradeep M M, Escalation Engineer, SQL Server Support

Featured Database Articles

Database User and Programming Tips

Posted February 1, 2018

By Greg Larsen

If you have a database backup of a Transparent Data Encryption (TDE) enabled database, the database backup will contain encrypted data. Because the database backup contains encrypted data you can’t just restore it to any instance. You can only restore the database backup to an instance that contains the same certificate used to originally encrypt the database.

Ssrs Generate A New Encryption Key Software

If you want to restore an encrypted database backup to a new instance you need to import the certificate from the source instance where the encrypted backup was created. Here are the steps it takes to copy the original certificate to the instance where the TDE enabled backup will be restored.

Step 1: Verify that there is a Database Master Key

Ssrs Generate A New Encryption Key In Computer

In this step you need to verify that the target server for the restore has a Database Master Key created. To verify that the Database Master key exists you can run the following TSQL code:

If a Database Master Key exists, then the above code will return the name of the Database Master key. If the Database Master Key doesn’t exist, then you can create it with the following TSQL code:

Step 2: Generate the Certificate Backup from Source Instance

In order to move a TDE encrypted database to another instance you need to have a backup of the certificate that was used to encrypt the TDE enabled database being moved. Hopefully when TDE was set up on the source server a certificate backup was taken. If not, then you can run this TSQL code on the source instance to create a certificate backup and a private key file:

This code backs up the certificate name TDE_CERT_for_MyData and createstwo files. The first file TDE_Cert_For_MyDate.cer contains the backup of the certificate.The second file TDE_CertKey.pvk contains the private key.

Ssrs Machine Key

Step 3: Restore Certificate to the Target instance

This code can be used to restore the certificate backup.

Ssrs Generate A New Encryption Key Download

Once the target instance contains the certificate that was used to encrypt the database being restored, then you will be able to restore your TDE enabled database backup to the target instance.

Ssrs Generate A New Encryption Key Generator

Ssrs Encryption Key