Encrypting data across replication

We are looking at setting up peer-to-peer transactional replication between two databases. We have a customer requirement to encrypt the SSN in this database. I have configured replication successfully. I have also successfully encrypted the SSN using a symmetric key (with encryption by certificate). What I haven't done yet is set up encryption to work across a replication topology.

What steps would I have to follow in order to be able to encrypt the SSN on one server, replicate it to the subscriber, and then decrypt the SSN on the subscriber? For this scenario, is there a better way to handle encryption other than a symmetric key encrypted by a certificate?

The most important thing is to set up your encryption key so that you can manually recreate it on the subscriber. The setup must be manual, because replication will only work for the content of the tables, not for the keys used to encrypt that content. Have a look at this post for additional information: http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx.

Thanks
Laurentiu

|||As a followup question, I have one field -- SSN -- that appears in several tables. I also have another field that needs to be encrypted. Is there any "rule of thumb" to use when deciding whether to create separate certificates/symmetric keys for each of these fields?

Encrypting data across replication

We are looking at setting up peer-to-peer transactional replication between two databases. We have a customer requirement to encrypt the SSN in this database. I have configured replication successfully. I have also successfully encrypted the SSN using a symmetric key (with encryption by certificate). What I haven't done yet is set up encryption to work across a replication topology.

What steps would I have to follow in order to be able to encrypt the SSN on one server, replicate it to the subscriber, and then decrypt the SSN on the subscriber? For this scenario, is there a better way to handle encryption other than a symmetric key encrypted by a certificate?

The most important thing is to set up your encryption key so that you can manually recreate it on the subscriber. The setup must be manual, because replication will only work for the content of the tables, not for the keys used to encrypt that content. Have a look at this post for additional information: http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx.

Thanks
Laurentiu

|||As a followup question, I have one field -- SSN -- that appears in several tables. I also have another field that needs to be encrypted. Is there any "rule of thumb" to use when deciding whether to create separate certificates/symmetric keys for each of these fields?

enabling AWE

After enabling awe and setting the max server memory, the
Task Manager, Process for sqlserver.exe shows 90MB - is
this normal? I have configured sql server to use 6GB!!!!
How can I be sure the memory is set up correctly?
CBUse perfmon and view the sql server memory counters instead of task manager.
Specifically the target and total memory should show approx 6GB. Did you
set the /PAE switch in the boot.ini as well? How about the /3GB?
87 Shrinking TempDB
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
--
Andrew J. Kelly SQL MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:64e201c4cb40$8dab3650$a601280a@.phx.gbl...
> After enabling awe and setting the max server memory, the
> Task Manager, Process for sqlserver.exe shows 90MB - is
> this normal? I have configured sql server to use 6GB!!!!
> How can I be sure the memory is set up correctly?
> CB

enabling AWE

After enabling awe and setting the max server memory, the
Task Manager, Process for sqlserver.exe shows 90MB - is
this normal? I have configured sql server to use 6GB!!!!
How can I be sure the memory is set up correctly?
CBUse perfmon and view the sql server memory counters instead of task manager.
Specifically the target and total memory should show approx 6GB. Did you
set the /PAE switch in the boot.ini as well? How about the /3GB?
87 Shrinking TempDB
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
Andrew J. Kelly SQL MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:64e201c4cb40$8dab3650$a601280a@.phx.gbl...
> After enabling awe and setting the max server memory, the
> Task Manager, Process for sqlserver.exe shows 90MB - is
> this normal? I have configured sql server to use 6GB!!!!
> How can I be sure the memory is set up correctly?
> CB

enabling AWE

After enabling awe and setting the max server memory, the
Task Manager, Process for sqlserver.exe shows 90MB - is
this normal? I have configured sql server to use 6GB!!!!
How can I be sure the memory is set up correctly?
CB
Use perfmon and view the sql server memory counters instead of task manager.
Specifically the target and total memory should show approx 6GB. Did you
set the /PAE switch in the boot.ini as well? How about the /3GB?
87 Shrinking TempDB
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
Andrew J. Kelly SQL MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:64e201c4cb40$8dab3650$a601280a@.phx.gbl...
> After enabling awe and setting the max server memory, the
> Task Manager, Process for sqlserver.exe shows 90MB - is
> this normal? I have configured sql server to use 6GB!!!!
> How can I be sure the memory is set up correctly?
> CB

Enable the CLR at database level only

Hi,

Is there a way to allow specific databases access to the CLR. Currently, my understanding is that when this setting is enabled, it applies to all databases within the instance.

Kind regards,

Jan.

CLR is enabled by instance, you could sepearate the database by instances whereas one would allow, the other one would t allow the CLR.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Enable SSL

I have read the MSDN articles about setting up SSL on the SQL server. I am
running Microsoft Certificate services on my network as well. The article t
alks about the following
Enter the fully qualified domain name of your computer in the Name: text box
. Ping your computer to get the fully qualified domain name if you are not s
ure what it is.
In the Intended Purpose section, change the selection to Server Authenticati
on Certificate by using the drop-down list box from the Client Authenticatio
n Certificate.
I cannot seem to find these options anywhere...How do I change the FQDN if I
have no name field...I don't even have an intended purpose dropdown list.
I can issue a computer certificate from the MMC on the SQL Server but it doe
s not use the FQDN of the c
luster I am trying to setup. How can I get this to work if I cannot see the
options that these articles are telling me? I am logging in with Domain Ad
min rights as well.
Ignore my last post...I forgot my email address...That article is if you make a http request to a Standalone Certificate
Cerver.
If you have a standalone CA you have to use http request to request the
cert. Follow this article:
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you installed the Enterprise CA as part of Active Directory, then you
can use MMC. Follow this article.
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Enable Report Builder for User

I cannot figure out how to display the 'Report Builder' icon in the toolbar for a user.

I've tried setting the different security roles, but the icon doesn't display.

Thanks.

I guess what I should have asked was -- "How do I deploy Report Builder to a user?"

I've found that this URL will execute Report Builder -- http://server name/reportserver/reportbuilder/reportbuilder.application

What is the best way to deploy ReportBuilder to a user?

|||

Hi,

If I am correct, you want the user to build his own ad-hoc reports using the report model. If this is correct, when the user click on the report builder, he will be able to select the report models (already deployed on the production server).

When user selects the report model and the format for the report, the report builder is available to the user as clickonce application. It would require .NET framework to be available on the user's machine as prerequiste.

Hope this helps.

Virendra

|||

Hi Virendra,

I am having the same issue with Report Builder as Pwomack.

Like Pwomack i found that same link to http://<<server>>/reportserver/reportbuilder/reportbuilder.application although i couldn't use it because our users work with Res Powerfuse in a Terminal Server environment (users don't have access to the local C: drive and .Net Framework tries to place Tmp files in Documents & Settings\username\local settings\... which is prohibited in our environment / infrastructure.)

I've been testing and i've tried to adjust user rights and such but without luck. The .Net framework is installed on our servers. Do you have any other suggestions where i could look?

With regards,

Gawelles

Enable Report Builder for User

I cannot figure out how to display the 'Report Builder' icon in the toolbar for a user.

I've tried setting the different security roles, but the icon doesn't display.

Thanks.

I guess what I should have asked was -- "How do I deploy Report Builder to a user?"

I've found that this URL will execute Report Builder -- http://server name/reportserver/reportbuilder/reportbuilder.application

What is the best way to deploy ReportBuilder to a user?

|||

Hi,

If I am correct, you want the user to build his own ad-hoc reports using the report model. If this is correct, when the user click on the report builder, he will be able to select the report models (already deployed on the production server).

When user selects the report model and the format for the report, the report builder is available to the user as clickonce application. It would require .NET framework to be available on the user's machine as prerequiste.

Hope this helps.

Virendra

|||

Hi Virendra,

I am having the same issue with Report Builder as Pwomack.

Like Pwomack i found that same link to http://<<server>>/reportserver/reportbuilder/reportbuilder.application although i couldn't use it because our users work with Res Powerfuse in a Terminal Server environment (users don't have access to the local C: drive and .Net Framework tries to place Tmp files in Documents & Settings\username\local settings\... which is prohibited in our environment / infrastructure.)

I've been testing and i've tried to adjust user rights and such but without luck. The .Net framework is installed on our servers. Do you have any other suggestions where i could look?

With regards,

Gawelles