24 votes
Accepted

The certificate chain was issued by an authority that is not trusted

On connection pop up click on options Then make sure the "Trust server certificate" is checked
user avatar
  • 356
11 votes
Accepted

What Microsoft DBA certificate should I acquire?

The MCSA certifications are being retired very soon. This certification retires on January 31, 2021. You will no longer be able to earn this certification after this date. I would not recommend ...
user avatar
  • 27.2k
8 votes
Accepted

Permissions in triggers when using cross database certificates

The problem here is that while the Certificate links the stored procedure in DatabaseA with the User in DatabaseB that has INSERT permissions on the two tables, the Trigger on the table being inserted ...
user avatar
8 votes
Accepted

SQL Server certificate for backups: Does expiration date matter?

I have tested this on SQL Server 2016 (Developer Edition, though that shouldn't matter), and can confirm: No, after the Certificate expires, using it to encrypt a backup will cause the following ...
user avatar
8 votes
Accepted

SQL Server SSL Encryption - SelfSign Cert working - why?

Traffic will be encrypted regardless of the client encryption specification when you turn on the Force Encryption option on the server side (SSCM-->SQL Server Network Configuration-->Protocols for ...
user avatar
  • 24.3k
7 votes
Accepted

Swapping TDE Cert Slow, any way to improve performance?

Is there a faster way to change TDE Certificates that anyone knows of? If you're actually needing to change the certificate that is doing the encryption (it protects the data) then the only faster ...
user avatar
7 votes
Accepted

Is it possible to add a certificate to a database role?

No, it is not possible to create a Role from a CERTIFICATE or ASYMMETRIC KEY like you can with Logins and Users. It is also not necessary to do this nor would it even make sense to do this. And the ...
user avatar
7 votes
Accepted

Stored procedure security with execute as, cross database queries, and module signing

You were headed in the right direction and were very close. Now you just need to view the module signing as a replacement of EXECUTE AS instead of as something that is added to it. Removing EXECUTE AS ...
user avatar
6 votes

The certificate chain was issued by an authority that is not trusted

go advanced "options" and put TrustServerCertificate=True to Additional Connection Parameters
user avatar
  • 61
6 votes
Accepted

Error: "The certificate cannot be dropped because one or more entities are either signed or encrypted using it."

To find items associated with Certificates and Asymmetric Keys, first try the query posted in this DBA.SE Answer: Find signed procedures, functions, triggers, assemblies and by which certificates / ...
user avatar
5 votes
Accepted

Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys

The easiest way seems to be using the sys.crypt_properties Catalog View which holds the relationship between Certificates / Asymmetric Keys and signed modules, as well as the usage (i.e. Signature or ...
user avatar
5 votes
Accepted

Impersonate user or login mapped to certificate

Logins and Users created from Certificates and Asymmetric Keys cannot be impersonated. They are merely proxies for a set of permissions that will be added to any module that is signed with the same ...
user avatar
5 votes
Accepted

Permissions issue in Docker SQL Server 2017 while restoring certificate

Is the code shown under "Relevant code:" (in the question) the exact code being executed, as in copied and pasted into here and not re-typed? I ask because the file extension on the ...
user avatar
5 votes
Accepted

Is an SSL Certificate Authority Chain "Secret"

You do not need to treat that certificate chain as secret. It is just like the public cert you can view when connecting to a website like stack overflow. The private certificate is being used server ...
user avatar
4 votes
Accepted

Microsoft Key Storage Provider for Always Encrypted Master Key

How can I remove the "Always-Encrypted-Auto1" entry? Since there isn't a way in the SSMS GUI and I doubt you want to write your own code to find out, look in %APPDATA%\Microsoft\Crypto\Keys and ...
user avatar
4 votes
Accepted

SSDT Schema Comparison for Stored Procedure signed by a Certificate

Before getting into any specifics about Certificate creation and module signing, we can at least verify whether or not the two instances have the same certificate. Run the following on both instances: ...
user avatar
3 votes
Accepted

A SQL Server database backup/restore issue

If you have a copy of the master database which holds the DBBackupEncryptCert then you'll want to restore that and grab the copy of the certificate, then import it into your current instance for use. ...
user avatar
3 votes

SQL over TLS using Let's Encrypt on Raspberry pi

I'm a bit late replying here but I suspect the problem is that Let's Encrypt supplies certificates in PKCS #8 format, whereas MySQL needs them in PKCS #1 format. You will need to convert the key each ...
user avatar
3 votes
Accepted

Creating encrypted backups inhouse to be restored at client

Created the certificate from the .cer-file […] You created the certificate but you didn't include the import of the private key, so it makes the use of this certificate to decrypt the data impossible....
user avatar
3 votes
Accepted

SQL Server Encryption with "localhost" certificate

Just for fun, I tried to do this with a wildcard certificate. The fact that anyone can do this with a self-signed certificate, for a local application, being forced to use SSL when shared memory would ...
user avatar
3 votes

Why would I download CA certificate when starting a managed Postgres cluster on DigitalOcean?

You can use it with TLS to verify that you are connecting to the correct server. The connection string for that would look like: host=... port=... dbname=... user=... sslmode=verify-full sslrootcert=/...
user avatar
  • 33.2k
3 votes

Changing Database Encryption Keys

Most of this is answered in another post, but I will not vote this as duplicate as you ask if the data will be "readable" or unencrypted at anytime during the process. Regenerating the ...
user avatar
  • 4,534
3 votes
Accepted

How do I generate the private key needed to restore the encrypted backup?

The private key is used as part of the BACKUP CERTIFICATE syntax. Per the docs, the syntax is: BACKUP CERTIFICATE certname TO FILE = 'path_to_file' [ WITH PRIVATE KEY ( FILE =...
user avatar
  • 14.4k
2 votes
Accepted

Configure SQL Server to encrypt data traffic in both directions

There are two possible ways to ensure that you encrypt connections from clients to the SQL Server. The first option is to setup IPsec either manually on both clients and server or by using a IPSec ...
user avatar
  • 4,534
2 votes

Is there any reason to back up these system-created certificates?

Your first certificate needs to be backed up if you are moving the MSDB between instances. https://support.microsoft.com/en-us/kb/2000274 In short, its not typically required to explicitly backup ...
user avatar
  • 67.2k
2 votes

Permissions to create certificate

There is a CREATE CERTIFICATE permission: GRANT CREATE CERTIFICATE TO [user_or_role_name]; Here is a simple test: USE [tempdb]; CREATE USER [t] WITHOUT LOGIN; EXECUTE AS USER = 't'; PRINT '...
user avatar
2 votes
Accepted

The certificate, asymmetric key, or private key file is not valid or does not exist

Here are the steps that I had to take to restore the encrypted database onto a different server(localhost). 1.Create the certificates : CREATE CERTIFICATE [CertficateName] FROM FILE = 'C:\...
user avatar
  • 279
2 votes

Signing a User-Defined Function with a certificate in SQL Server

The documentation calls out that Inline table-valued functions cannot be signed
user avatar
  • 24.3k
2 votes
Accepted

Having a backup of the master key, how can I restore a database that was encrypted by master key?

The screenshots show a backup of the service master key for the instance but that is not the same as the database master key that the AAG wizard is asking for. See Encryption Hierarchy in SQL. Your ...
user avatar
  • 9,287
2 votes
Accepted

Is SQL Server Certificate using asymmetric encryption internally?

In Books Online I've read that both asymmetric key and certificates use the idea of asymmetric encryption. They both are asymmetric keys, certificates just have extra metadata and can have other ...
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible