Tuesday, March 27, 2012

Error when trying to create a linked server

Hi all,

I have a problem when i try to create a linked server to a MsAccess Db. That's what i do:

linked server drop (in case it already exist)

sp_dropserver 'XXXXX', 'droplogins'

linked server creation

sp_addlinkedserver XXXXX, 'Jet 4.0','Microsoft.Jet.OLEDB.4.0', '\ \server\folder\db1.mdb'

login creation

sp_addlinkedsrvlogin XXXXX, FALSE, NULL, Admin, NULL

fill tables

sp_tables_ex XXXXX

File Db1.mdb is located on a partition (D) of the server where Sql is installed.

On login creation i get the following error :

Error -2147217900 Error during decryption. (15466) Source: Microsoft OLE DB Provider for SQL Server.

(This is not the real error message, it has been translated)

Some ideas?

Thanks


It seems like the system is having problems decrypting the service master key. Have you moved the master DB from one machine to another and/or changed the service service account for SQL server? If you changed the service account, try using the original account.

You can also try the following:

SELECT * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'

The information from this DMV will help us find out to determine if indeed it was the SMK decryption or something else.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

I must verify but i think that the customer has Sql Express 2005 SP1 installed. It's ok for retriving information or he has to install SP2?

Thanks

Peo

|||

Unfortunately in order to use the ring buffer DMV you need SQL Server SP2, but that information would only help us to confirm if it was the SMK the one that failed to be decrypted or if it something else. Feel free to update to SP2 if you consider it is necessary

The SMK is protected using DPAPI, and it is linked to the machine and the service account. Hopefully the following questions will help us to find out the root cause of the problem:

· Was the master DB created in a different machine? (i.e. recovered by a RESTORE operation)

· Has the service account for SQL Server service changed?

· What type of account is SQL Server service running as (i.e. domain account, local machine account, local system, network service, etc.)?

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

No comments:

Post a Comment