Re: SQL Server 2000 / 2005 Encryption

Ok, encrypting the connection makes sense to me. So on to the data at rest.

2) You're also talking about encrypting the data stored "at rest" in the database. This is possible on SQL 2005 via built-in T-SQL statements and functions, but requires 3rd-party tools, usually some sort of extended procedures (XPs).

You require 3rd-party tools even when using the native SQL 2005 encryption to encrypt the data "at rest"? I thought SQL 2005 supported that natively, but SQL 2000 required the 3rd party tools to accomplish this?

The certificates used to encrypt your data stored on SQL Server 2005 and to encrypt your SSL connections will be different. SQL Server can generate its own self-signed certificates though, and that should make the SSL encryption/connectivity easier for you. Excel does not know how to decrypt data stored in SQL Server 2005 in encrypted form.

Ok, I can figure out the details on the certificates when I get to that point. But you mentioned Excel cannot decrypt this information. If we have end users using Tablet PC's that need to decrypt this data, are they going to have to use a T-SQL statement calling the decrypting functions / XP's through QA or SSMS to get it? Or is there a means of having this done seamlessly for them? They are not technical at all.

Many thanks again Mike,
Rubens


"Mike C#" <xyz@xxxxxxx> wrote in message news:OTlzEyYfIHA.6136@xxxxxxxxxxxxxxxxxxxxxxx

"Rubens" <rubensrose@xxxxxxxxxxx> wrote in message news:CA7939A8-A98C-4202-9CC2-8D7052735B8E@xxxxxxxxxxxxxxxx
Excellent, thank-you Mike! This definitely points me in the right direction. Let me take a step back and explain what I am trying to accomplish and see if this makes sense. We will initially be doing this on a SQL 2000 machine.

We have data in a table that needs to be encrypted. Ultimately, this information needs to end up on a Tablet PC in Excel for users that have permission. So here is how I think we can accomplish this.

After I go through the steps you listed below, we need to distribute the certificate to the Tablet PC's and install it.
Once it is on the Tablet, does Excel just know to use this certificate because the information in the data dump / pivot table needs to be decrypted?
Is the same certificate used to secure the connection as well as decrypting the data in the table?

Does this make sense?


We need to separate these two issues you're talking about here:

1) You're talking about encrypting the connection to SQL Server, which is possible on SQL 2000 and SQL 2005 through SSL.

2) You're also talking about encrypting the data stored "at rest" in the database. This is possible on SQL 2005 via built-in T-SQL statements and functions, but requires 3rd-party tools, usually some sort of extended procedures (XPs).

The certificates used to encrypt your data stored on SQL Server 2005 and to encrypt your SSL connections will be different. SQL Server can generate its own self-signed certificates though, and that should make the SSL encryption/connectivity easier for you. Excel does not know how to decrypt data stored in SQL Server 2005 in encrypted form.

.

Relevant Pages

  • RE: local admin account password
    ... > encrypt the database and create alerts in the event of unsuccessful ... >> no more recovery console and don't think cached logins will work. ... >> The DB file would be encrypted with EFS so only the limited user SQL ... >> itself doesn't really need to be secure as the authentication is based ...
    (Focus-Microsoft)
  • Re: SQL Server 2000 / 2005 Encryption
    ... You require 3rd-party tools even when using the native SQL 2005 encryption ... to encrypt your SSL connections will be different. ... But you mentioned Excel cannot decrypt this information. ... Requests to the web services would connect to the database and invoke ...
    (microsoft.public.sqlserver.security)
  • Re: Error 17827 from SQL Server 2005 Express Edition
    ... How many connections are being made to your SQL Express instance? ... encrypt a password. ... The connection has been closed. ... [CLIENT: <local ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Server Encrypt, Failure..
    ... This function doesn't encrypt at ... (Not that I had too high of expectations for a undocumented SQL ... Declare @Password2 nvarchar ... > Bill Dodd ...
    (microsoft.public.sqlserver.server)
  • Re: Web.config encryption in shared hosting scenario
    ... I just begin to search for a solution because the customer does not allow ... like to encrypt the database connection string located in the web.config. ... I am connecting to the SQL ...
    (microsoft.public.dotnet.framework.aspnet.security)