Copy database with encrypted column to new server and decrypt column there

To do this successfully do I need to backup the Service master, Database master, and database itself from the the Source server, then restore all three of them on the destination server?

(I'm concerned that restoring the source Service Master key to a new target server with an existing sql 2005 install will screw things up big time.)

TIA,

Barkingdog

It's actually a little easier than that. All you have to do is restore the database and then restore the encryption between the SMK and the DBMK. The database master key should already be in the database so after that you just need to associate the DBMK with the new server's service master key.

After restore, you will need to:

1) use <database_name>

2) open database master key

3) alter database master key add encryption by service master key

After that, everything should work as normal.

Sung

Coonection to SQL Server 2005 Express

What is the proper connection string for SQL Server 2005 Express on XP?
I want to connect to the master database by VBScript as the following:
Dim computer : computer = CreateObject("WScript.Network").ComputerName
Dim connectionString:connectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Master;Data
Source=" + computer
Dim connection
Set connection= CreateObject("ADODB.Connection")
With connection
.Provider = "SQLOLEDB"
.ConnectionString = connectionString
.Open
End With
It works on MSDE. But I got a "[DBNETLIB][ConnectionOpen (Connect()).]SQL
Server does not exist or access denied. " error for SQL Express. I have the
TCP/IP enabled for SQL Server Express too.
Thanks.
take help from KBA
http://support.microsoft.com/default...;EN-US;Q328306 and
http://www.connectionstrings.com/ for all types of connection strings.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"Roy" wrote:

> What is the proper connection string for SQL Server 2005 Express on XP?
> I want to connect to the master database by VBScript as the following:
> Dim computer : computer = CreateObject("WScript.Network").ComputerName
> Dim connectionString:connectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=Master;Data
> Source=" + computer
> Dim connection
> Set connection= CreateObject("ADODB.Connection")
> With connection
> .Provider = "SQLOLEDB"
> .ConnectionString = connectionString
> .Open
> End With
> It works on MSDE. But I got a "[DBNETLIB][ConnectionOpen (Connect()).]SQL
> Server does not exist or access denied. " error for SQL Express. I have the
> TCP/IP enabled for SQL Server Express too.
> Thanks.

Coonection to SQL Server 2005 Express

What is the proper connection string for SQL Server 2005 Express on XP?
I want to connect to the master database by VBScript as the following:
Dim computer : computer = CreateObject("WScript.Network").ComputerName
Dim connectionString:connectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Master;Data
Source=" + computer
Dim connection
Set connection= CreateObject("ADODB.Connection")
With connection
.Provider = "SQLOLEDB"
.ConnectionString = connectionString
.Open
End With
It works on MSDE. But I got a "[DBNETLIB][ConnectionOpen (Connect())
.]SQL
Server does not exist or access denied. " error for SQL Express. I have the
TCP/IP enabled for SQL Server Express too.
Thanks.take help from KBA
http://support.microsoft.com/defaul...b;EN-US;Q328306 and
http://www.connectionstrings.com/ for all types of connection strings.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"Roy" wrote:

> What is the proper connection string for SQL Server 2005 Express on XP?
> I want to connect to the master database by VBScript as the following:
> Dim computer : computer = CreateObject("WScript.Network").ComputerName
> Dim connectionString:connectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=Master;Data
> Source=" + computer
> Dim connection
> Set connection= CreateObject("ADODB.Connection")
> With connection
> .Provider = "SQLOLEDB"
> .ConnectionString = connectionString
> .Open
> End With
> It works on MSDE. But I got a "[DBNETLIB][ConnectionOpen (Connect(
)).]SQL
> Server does not exist or access denied. " error for SQL Express. I have th
e
> TCP/IP enabled for SQL Server Express too.
> Thanks.