Welcome to dbFreaks.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Problem after disaster recovery

  
   Database Help (Home) -> Security RSS
Next:  Searching multiple fields  
Author Message
Pitcairnia

External


Since: Dec 30, 2008
Posts: 2



(Msg. 1) Posted: Tue Dec 30, 2008 12:32 pm
Post subject: Problem after disaster recovery
Archived from groups: microsoft>public>sqlserver>security (more info?)
A serious system crash took out one of our db servers. We were able to
recover all the databases including the system databases. The operating
system was reinstalled folowed by SQL Server 2005. I shut down the services
and copied the database files and log files into the data directory. When the
services were restarted, the databases were all present and usable with the
following exception.

There are a number of views in the database which use a linked server named
ADSI which gets Active Directory data. Any time any of these views are
queried the following error is returned:

An error occurred during decryption.

Additionally I can drop the views but I cannot recreate the views. I can
drop the linked server but cannot recreate it. Actually that's not exactly
accurate. I can recreate it (sp_addlinkedserver) but cannot add the login
(sp_addlinkedsrvlogin).

Any help greatly appreciated, I need to get this back online ASAP.

 >> Stay informed about: Problem after disaster recovery 
Back to top
Login to vote
Uri Dimant

External


Since: Aug 24, 2003
Posts: 1744



(Msg. 2) Posted: Wed Dec 31, 2008 4:25 am
Post subject: Re: Problem after disaster recovery [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Hi
So what has changed since you re-installed? What errors do you get?

"Pitcairnia" wrote in message

>A serious system crash took out one of our db servers. We were able to
> recover all the databases including the system databases. The operating
> system was reinstalled folowed by SQL Server 2005. I shut down the
> services
> and copied the database files and log files into the data directory. When
> the
> services were restarted, the databases were all present and usable with
> the
> following exception.
>
> There are a number of views in the database which use a linked server
> named
> ADSI which gets Active Directory data. Any time any of these views are
> queried the following error is returned:
>
> An error occurred during decryption.
>
> Additionally I can drop the views but I cannot recreate the views. I can
> drop the linked server but cannot recreate it. Actually that's not
> exactly
> accurate. I can recreate it (sp_addlinkedserver) but cannot add the login
> (sp_addlinkedsrvlogin).
>
> Any help greatly appreciated, I need to get this back online ASAP.
>

 >> Stay informed about: Problem after disaster recovery 
Back to top
Login to vote
Dan Guzman1

External


Since: Aug 22, 2004
Posts: 840



(Msg. 3) Posted: Wed Dec 31, 2008 7:22 am
Post subject: Re: Problem after disaster recovery [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Are you using the same domain service account as the original install? If
not, you'll need to restore the service master key from a key backup file so
that server level secrets like linked server passwords can be
encrypted/decrypted. If you have no master key backup file and cannot use
the original service account (e.g. it was a local account of the original OS
install), you'll need to regenerate the master key. See ALTER MASTER KEY in
the Books Online for details.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Pitcairnia" wrote in message

>A serious system crash took out one of our db servers. We were able to
> recover all the databases including the system databases. The operating
> system was reinstalled folowed by SQL Server 2005. I shut down the
> services
> and copied the database files and log files into the data directory. When
> the
> services were restarted, the databases were all present and usable with
> the
> following exception.
>
> There are a number of views in the database which use a linked server
> named
> ADSI which gets Active Directory data. Any time any of these views are
> queried the following error is returned:
>
> An error occurred during decryption.
>
> Additionally I can drop the views but I cannot recreate the views. I can
> drop the linked server but cannot recreate it. Actually that's not
> exactly
> accurate. I can recreate it (sp_addlinkedserver) but cannot add the login
> (sp_addlinkedsrvlogin).
>
> Any help greatly appreciated, I need to get this back online ASAP.
>
 >> Stay informed about: Problem after disaster recovery 
Back to top
Login to vote
Pitcairnia

External


Since: Dec 30, 2008
Posts: 2



(Msg. 4) Posted: Wed Dec 31, 2008 9:02 am
Post subject: Re: Problem after disaster recovery [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Thank you for your prompt reply. I am unsure that the account is the same, my
guess is that it is not. When doing the reinstall I checked to see what all
the other db servers were using, then I used that same domain account, but
that does not necesssarily mean it is what the old one was running... I
might document this db better and start backing up this key... Smile

When I try this command against one of the user databases

ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD =
'strongpassword'

I get this error

Msg 15151, Level 16, State 1, Line 1
Cannot find the symmetric key 'master key', because it does not exist or you
do not have permission.

I am logged in to the sa account.

Any further help with this issue vastly appreciated.





"Dan Guzman" wrote:

> Are you using the same domain service account as the original install? If
> not, you'll need to restore the service master key from a key backup file so
> that server level secrets like linked server passwords can be
> encrypted/decrypted. If you have no master key backup file and cannot use
> the original service account (e.g. it was a local account of the original OS
> install), you'll need to regenerate the master key. See ALTER MASTER KEY in
> the Books Online for details.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Pitcairnia" wrote in message
>
> >A serious system crash took out one of our db servers. We were able to
> > recover all the databases including the system databases. The operating
> > system was reinstalled folowed by SQL Server 2005. I shut down the
> > services
> > and copied the database files and log files into the data directory. When
> > the
> > services were restarted, the databases were all present and usable with
> > the
> > following exception.
> >
> > There are a number of views in the database which use a linked server
> > named
> > ADSI which gets Active Directory data. Any time any of these views are
> > queried the following error is returned:
> >
> > An error occurred during decryption.
> >
> > Additionally I can drop the views but I cannot recreate the views. I can
> > drop the linked server but cannot recreate it. Actually that's not
> > exactly
> > accurate. I can recreate it (sp_addlinkedserver) but cannot add the login
> > (sp_addlinkedsrvlogin).
> >
> > Any help greatly appreciated, I need to get this back online ASAP.
> >
>
>
 >> Stay informed about: Problem after disaster recovery 
Back to top
Login to vote
Dan Guzman1

External


Since: Aug 22, 2004
Posts: 840



(Msg. 5) Posted: Thu Jan 01, 2009 9:44 pm
Post subject: Re: Problem after disaster recovery [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
> When I try this command against one of the user databases
>
> ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD =
> 'strongpassword'
>
> I get this error
>
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the symmetric key 'master key', because it does not exist or
> you
> do not have permission.

I should have made it clear that it is the service master key that needs to
be regenerated. You might try changing the service account using the SQL
Server Configuration Manager tool as this will regenerate the service master
key. It that doesn't work, try it with the
ALTER SERVICE MASTER KEY REGENERATE command. I don't think the FORCE option
is needed in your situation.

Note that encrypted data will be unrecoverable after the service master key
is regenerated. This shouldn't be a big deal if the only encrypted data is
linked server passwords since all you'll need to do is recreate using
sp_addlinkedsrvlogin.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
 >> Stay informed about: Problem after disaster recovery 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
What type of Server Roles to assign for Backup and Recovery - Hi there, I have a question , what is the best practice to create a login to perform backup and recovery, Can I just have DB role as DB_backupoperator or I have to assign a sysadmin role to the user ( in SQL authentication/ windows).Coming up with a....

problem with space - HI friends, I've got one question. When I realize "log in" in my sql server, I am using SP like this: CREATE Procedure CustomerLogin ( @Email nvarchar(30), @Pwd nvarchar(10), @CustomerID int OUTPUT ) AS SELECT ...

Authentication problem - Hi, First of all I would like to thank you in advance for all your time given to my problem. I'll try to explain our problem. In our network we have several servers all with W2K SERVER. SERVER 1 is DCP SERVER 2 is DC SERVER 3 is running SQL server..

Facing the same problem - I am facing the same (well, similar) issue as the OP. Here's the scenario. My stored procedure is being activated from a Service Broker queue. Within the stored procedure, it is trying to do some operations across a Linked server, which is failing with..

New SQL Security problem coming next Tuesday - Hi, Yesterday's Security Bulletin Advanced Notice http://www.microsoft.com/technet/security/bulletin/ms08-jul.mspx talks about a patch coming for SQL 7/2000/2005. Wonder if this will be in CU9 for SQL2005? If it is extra to CU7 for SQL2005 it will..
   Database Help (Home) -> Security All times are: Pacific Time (US & Canada)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]