Windows 10: Fix: Cannot find server certificate with thumbprint while restoring SQL database

When restoring a database you might get the below error.

System.Data.SqlClient.SQLError: Cannot find server certificate with thumbprint



This is because the database was encrypted with Transparent Data Encryption (TDE) and you will not be able to restore it until you get the Certificate, the Private key and the password from the supplier of the database.

After you collect the required items above, open a new SQL query as the server admin on the database master.

First we need to create the master cerificate on the server by using

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<MyComplexPassword>'
GO

Now that the master certificate has been installed you will need to install the certificate provided by the owner of the database

CREATE CERTIFICATE MyServerCert
FROM FILE = 'C:\TDECert\Cert.cer'
WITH PRIVATE KEY (FILE = 'C:\TDECert\key.pvk',
DECRYPTION BY PASSWORD = '<PasswordProvidedByTheSupplier>');

Once this is done and executed you will be allowed to restore the database.

(10479)

read more...

 

SQL Server 2017 Express Issue with 3 Tier database access.

Hi, I am facing an issue with SQL Server 2017 Express.

My client is on PC1, SQL server is configured on PC2 and the database is on PC3. I could successfully connect to SQL server on PC2 through client on PC1, but if I try to access the database on PC 3 using client on PC1, I get following error...

"Msg 5120, Level 16, State 101, Line 2

Unable to open the physical file "\\MD1Z549C.ad001.siemens.net\Database_DT\Products\ProductDB.mdf". Operating system error 5: "5(Access is denied.)".

Msg 1802, Level 16, State 7, Line 2

CREATE DATABASE failed. Some file names listed could not be created. Check related errors."

When i access the database on PC2 there is no Issue. or if i use other SQL server from PC1 then I could access database database on PC3 as well. (here no intermediate PC2 is used)

So issue is when I have Client, SQL Server and Database on 3 different machines. (it gives above errro message)

The same scenario used to work well with SQL server 2008 R2 Express.

The same issue is reproducible with SQL server 2017 full version and SQL server 2019.

All the three PCs, shared drives and SQL server are configured well, which used to work with SQL server 2008 R2 express.

Is there any limitation or restriction introduced with the latest version of SQL server ? OR anything I need to try out to make the scenario work on three different machine.

Thanks a lot in advance.

 

SQL server 2016 database audit specification problem

I install Sql server 2016 on standard mode.

now I want to configuration on auditing on SQL server database. I enable audit and server audit specification.

But I can not create database audit specification........

How can i do this ......

it shows that, this audit is not available for that sql server.

 

Running sql server 2005 and have restored several databases from backups using commands

Original Title: sql server 2005

I have a windows 10 computer running SQL server 2005 and have restored several databases from backups that were created using the following command:

BACKUP DATABASE @name TO DISK = @fileName WITH INIT

After restoring those databases, everything seems to run fine, but sys.dm_db_index_usage_stats is not being updated effectively. I can only get the stats to show up for 1 database at a time, and it only seems to be temporary. They are removed as soon as
the next database is updated. The last updated database will only stay there temporarily too. I am not detaching the databases or restarting the server or doing anything else that should reset the statistics. Any ideas?