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
- 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 ...
- 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 ...
- 65.8k
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 ...
- 65.8k
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 ...
- 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 ...
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 ...
- 65.8k
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 ...
- 65.8k
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
- 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 / ...
- 65.8k
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 ...
- 65.8k
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 ...
- 65.8k
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 ...
- 65.8k
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 ...
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 ...
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:
...
- 65.8k
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.
...
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 ...
- 31
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....
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 ...
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=/...
- 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 ...
- 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 =...
- 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 ...
- 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 ...
- 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 '...
- 65.8k
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:\...
- 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
- 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 ...
- 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 ...
Only top scored, non community-wiki answers of a minimum length are eligible