Encryption not enabled on Server

Trying to get5 SSL work between SQL server and Query Analyzer. I have enable
d
encryption on the Query Analyzer client and installed a certificate on SQL
server,
When I connect from QA, i get a message saying "Encrytion not supported on
SQL server".
I enabled "Force Protocol Encryption" on Server and disable encryption on
client.
Now SQL server lwon't start and log says "Encryption requested but no valid
certificate was found. SQL Server terminating."
I used MMC to install certificate on SQL server, on Windows 2000 Pro.
What is the correct way to install a valid cert on SQL 2000 server?Follow the steps here;
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you have Active Directory then
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.

Encryption not enabled on Server

Trying to get5 SSL work between SQL server and Query Analyzer. I have enabled
encryption on the Query Analyzer client and installed a certificate on SQL
server,
When I connect from QA, i get a message saying "Encrytion not supported on
SQL server".
I enabled "Force Protocol Encryption" on Server and disable encryption on
client.
Now SQL server lwon't start and log says "Encryption requested but no valid
certificate was found. SQL Server terminating."
I used MMC to install certificate on SQL server, on Windows 2000 Pro.
What is the correct way to install a valid cert on SQL 2000 server?
Follow the steps here;
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you have Active Directory then
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.

Encryption for sql server

hey ppl..
i am creating a client that can access straight into the sql db using vb.net.
is there a way encrypt the data from the client and the sql server will decrypt the data and visa versa?Your best choice is to use some flavor of VPN to get your data to the server safely. A poor alternative (although pure MS-SQL) is to use Named Pipes, which can do feeble encryption at relatively low performance.

-PatP|||is there anyway to make a simple encryption? small enough so that it encrypts the data and it does not effect performance?|||See if this helps you:

http://www.developerfusion.com/show/4647/

You would basically be storing the data encrypted. The problem is when you go to search the data of course. If you need to encrypt all traffic, you can look at what Pat said. I would recommend against it. There are also several hardward level tools for this, but they are expensive.

Encryption and the client

I have a basic understanding of the encryption using T-SQL, but is all of this at the T-SQL level? Like can a client have a certificate, be sent the encrypted value (that was encrypted on SQL Server), and using the same cert that is on the server decrypt the data?

Is there a good article/book that covered encryption in detail that you can suggest? Or is this something that you would build in .NET?

If your concern is to protect data in transit, I would strongly recommend you to use SSL to protect the data between your server and the client.

If your goal is to protect data at rest, but in such a way that the protected data cannot be decrypted by the server (i.e. the decryption key is never stored/used in the server hosting SQL Server) you can use .Net to protect the data directly, but all the key management should be on your client application.

Typically, for normal data I don’t recommend using a model where you use public keys to protect data directly on SQL Server, and use the private key on the client to decrypt it (or vice versa).

The main reasons are that your application will still have to do most of the key management (private/public keys), the performance of asymmetric key encryption is orders of magnitude slower than symmetric key encryption and that in SQL Server the limit for encrypting data using an asymmetric key is 1 block (based on the private key modulus, for the self signed certificates SQL Server generates this means you can only encrypt up to ~117 bytes of plaintext).

After saying that, there may be some cases where this mechanism may actually work better for your particular needs. You can find one sample on how to use the .Net framework to encrypt data and decrypt it on SQL Server using asymmetric keys: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384472&SiteID=1

I hope this information can help you.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Actually my question was largely academic in nature, as I was trying to get my head straight on a few topics with SQL Server for a client. I had a few preconcieved notions about how things worked with encryption.

Thanks again (you have helped me before :)!

Encryption

Do you guys encrypt data on SQL 2K? If you do, you prefer server side
(data sent to server and SQL encrypts data before saving) or client side
(data encrypted before sent to server? We are looking at encryption to
protect sensitive data such as SSN's and still don't know what is the right
way to start and what is the pros and cons for each approach? What tools do
you guys use for this purpose? Thanks for your input.Check the encryption section in this FAQ:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
Some of the third party product sites have white papers as
well.
-Sue
On Mon, 7 Mar 2005 10:10:21 -0800, "ME" <ME@.hotmail.com>
wrote:
>Do you guys encrypt data on SQL 2K? If you do, you prefer server side
>(data sent to server and SQL encrypts data before saving) or client side
>(data encrypted before sent to server? We are looking at encryption to
>protect sensitive data such as SSN's and still don't know what is the right
>way to start and what is the pros and cons for each approach? What tools do
>you guys use for this purpose? Thanks for your input.
>|||You might investigate the Microsoft Crypto API. The Interop.Capicom dll is
included in the Platform SDK
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3mls21drp8ss6noflbg3ujao1dt2l9gle0@.4ax.com...
> Check the encryption section in this FAQ:
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
> Some of the third party product sites have white papers as
> well.
> -Sue
> On Mon, 7 Mar 2005 10:10:21 -0800, "ME" <ME@.hotmail.com>
> wrote:
> >Do you guys encrypt data on SQL 2K? If you do, you prefer server side
> >(data sent to server and SQL encrypts data before saving) or client side
> >(data encrypted before sent to server? We are looking at encryption to
> >protect sensitive data such as SSN's and still don't know what is the
right
> >way to start and what is the pros and cons for each approach? What tools
do
> >you guys use for this purpose? Thanks for your input.
> >
>

Encryption

Do you guys encrypt data on SQL 2K? If you do, you prefer server side
(data sent to server and SQL encrypts data before saving) or client side
(data encrypted before sent to server? We are looking at encryption to
protect sensitive data such as SSN's and still don't know what is the right
way to start and what is the pros and cons for each approach? What tools do
you guys use for this purpose? Thanks for your input.Check the encryption section in this FAQ:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
Some of the third party product sites have white papers as
well.
-Sue
On Mon, 7 Mar 2005 10:10:21 -0800, "ME" <ME@.hotmail.com>
wrote:

>Do you guys encrypt data on SQL 2K? If you do, you prefer server side
>(data sent to server and SQL encrypts data before saving) or client side
>(data encrypted before sent to server? We are looking at encryption to
>protect sensitive data such as SSN's and still don't know what is the right
>way to start and what is the pros and cons for each approach? What tools d
o
>you guys use for this purpose? Thanks for your input.
>|||You might investigate the Microsoft Crypto API. The Interop.Capicom dll is
included in the Platform SDK
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3mls21drp8ss6noflbg3ujao1dt2l9gle0@.
4ax.com...
> Check the encryption section in this FAQ:
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
> Some of the third party product sites have white papers as
> well.
> -Sue
> On Mon, 7 Mar 2005 10:10:21 -0800, "ME" <ME@.hotmail.com>
> wrote:
>
right[vbcol=seagreen]
do[vbcol=seagreen]
>

Encryption

Do you guys encrypt data on SQL 2K? If you do, you prefer server side
(data sent to server and SQL encrypts data before saving) or client side
(data encrypted before sent to server? We are looking at encryption to
protect sensitive data such as SSN's and still don't know what is the right
way to start and what is the pros and cons for each approach? What tools do
you guys use for this purpose? Thanks for your input.
Check the encryption section in this FAQ:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
Some of the third party product sites have white papers as
well.
-Sue
On Mon, 7 Mar 2005 10:10:21 -0800, "ME" <ME@.hotmail.com>
wrote:

>Do you guys encrypt data on SQL 2K? If you do, you prefer server side
>(data sent to server and SQL encrypts data before saving) or client side
>(data encrypted before sent to server? We are looking at encryption to
>protect sensitive data such as SSN's and still don't know what is the right
>way to start and what is the pros and cons for each approach? What tools do
>you guys use for this purpose? Thanks for your input.
>
|||You might investigate the Microsoft Crypto API. The Interop.Capicom dll is
included in the Platform SDK
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3mls21drp8ss6noflbg3ujao1dt2l9gle0@.4ax.com... [vbcol=seagreen]
> Check the encryption section in this FAQ:
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
> Some of the third party product sites have white papers as
> well.
> -Sue
> On Mon, 7 Mar 2005 10:10:21 -0800, "ME" <ME@.hotmail.com>
> wrote:
right[vbcol=seagreen]
do
>
sql

encrypting connections to sql server 2005

can you please tell me how can i encrypt data sent between client application and sql server 2005.Hi,

thats quite easy, you can do this on the protocol layer:

http://support.microsoft.com/kb/316898/en-us

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||can i not enable encryption without installing any certificate?|||

No, a certificate is needed in order to use SSL. I am including additional links that may be useful.

· Encrypting Connections to SQL Server http://msdn2.microsoft.com/en-us/library/ms189067.aspx

· SSL Certificates http://msdn2.microsoft.com/en-gb/library/aa364691.aspx (This one makes reference to SSL for HTTP connections, but the main explanation and description of how SSL uses certificates are common)

· Configuring Certificate for Use by SSL http://msdn2.microsoft.com/en-us/library/ms186362.aspx

I hope this information will be useful.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

there is no must to buy a public trusted certificate if you don′t need it somewhere outside your company and that fits your secuirty needs, you can also create a certificate on your own.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

Encrypting confidential data including foreign keys

Hi,
I am currently working on a project for a client. The project is to create
an intranet site to maintain confidential employee data. There is a master
employee table, containing name, date of birth etc. However, the table also
links to some look-up tables, such as grades, ethnicity, citizenship etc.
I was wondering what was the best solution for encrypting the data in SQL
Server 2000, so that someone with database access cannot read the
confidential data. Encrypting the free text data is fairly simple, but I am
not sure how to encrypt the foreign keys while preserving referential
integrity.
For example, if an employee has citizenship = 5, it is quite easy to workout
the citizenship from the look-up table. Even if 5 is encrypted to ABC, it is
quite easy to see the pattern on the table.
Also, the client wants to do some reporting on the tables, so run queries
with filters (such as Citizenship=UK) on encrypted data.
We looked at xpcrypt, but it appears to create automatically a view
containing the decrypted data for the duration of the SQL session, so I am
not sure how appropriate it is for a web application, maintaining pretty
much a constant connection/session to the DB.
Any advice welcome!
Thanks,
TomOn Fri, 28 Oct 2005 21:29:31 +0100, "Tom" <Tom@.nospam.com> wrote:

> but I am
>not sure how to encrypt the foreign keys while preserving referential
>integrity.
>
Hmm, I doubt you'll find any encryption product that can encrypt a
foreign key column.
Scott
http://www.OdeToCode.com/blogs/scott/|||Hi,
well try our software for SQL server side encryption for that , visit and
refer :
http://database-encryption.com/ it will serve your purpose
Regards
--
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hi,
> I am currently working on a project for a client. The project is to create
> an intranet site to maintain confidential employee data. There is a master
> employee table, containing name, date of birth etc. However, the table als
o
> links to some look-up tables, such as grades, ethnicity, citizenship etc.
> I was wondering what was the best solution for encrypting the data in SQL
> Server 2000, so that someone with database access cannot read the
> confidential data. Encrypting the free text data is fairly simple, but I a
m
> not sure how to encrypt the foreign keys while preserving referential
> integrity.
> For example, if an employee has citizenship = 5, it is quite easy to worko
ut
> the citizenship from the look-up table. Even if 5 is encrypted to ABC, it
is
> quite easy to see the pattern on the table.
> Also, the client wants to do some reporting on the tables, so run queries
> with filters (such as Citizenship=UK) on encrypted data.
> We looked at xpcrypt, but it appears to create automatically a view
> containing the decrypted data for the duration of the SQL session, so I am
> not sure how appropriate it is for a web application, maintaining pretty
> much a constant connection/session to the DB.
> Any advice welcome!
> Thanks,
> Tom
>
>|||http://www.sqlservercentral.com/col...oolkitpart1.asp
You can't really encrypt foreign key columns to any useful degree, since
they'll have to be encrypted on the referenced table as well and once again
you've got an easily visible link... What you can do is encrypt the
descripition data in the referenced table to make it unreadable. So instead
of storing "NATIVE AMERICAN" in plain text on the ethnicity table, you could
encrypt that description and store it.
"Tom" <Tom@.nospam.com> wrote in message
news:raydnZJ8BOyXF__eRVnyjw@.pipex.net...
> Hi,
> I am currently working on a project for a client. The project is to create
> an intranet site to maintain confidential employee data. There is a master
> employee table, containing name, date of birth etc. However, the table
> also links to some look-up tables, such as grades, ethnicity, citizenship
> etc.
> I was wondering what was the best solution for encrypting the data in SQL
> Server 2000, so that someone with database access cannot read the
> confidential data. Encrypting the free text data is fairly simple, but I
> am not sure how to encrypt the foreign keys while preserving referential
> integrity.
> For example, if an employee has citizenship = 5, it is quite easy to
> workout the citizenship from the look-up table. Even if 5 is encrypted to
> ABC, it is quite easy to see the pattern on the table.
> Also, the client wants to do some reporting on the tables, so run queries
> with filters (such as Citizenship=UK) on encrypted data.
> We looked at xpcrypt, but it appears to create automatically a view
> containing the decrypted data for the duration of the SQL session, so I am
> not sure how appropriate it is for a web application, maintaining pretty
> much a constant connection/session to the DB.
> Any advice welcome!
> Thanks,
> Tom
>|||you can use built in database security to accomplish your goal|||What SQL 2000 function is that?
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1147967032.280063.193550@.u72g2000cwu.googlegroups.com...
> you can use built in database security to accomplish your goal
>

EncryptByPassPhrase and SQL Server Profiler

Hello,

My client wants to encrypt data using sql server 2005 encryption mechanism. But one of the core requirements is that the DBA should not be able to decrypt my data on the SQL Server.

I have opted for the EncryptByPassPhrase option. But here is my problem:

I call the encryptByPassPhrase function within my stored procedures and I pass in the pass phrase as a parameter in Stored Proc call from the client intranet application.

The problem is that one can clearly see the passphrase in the Profiler when I call my stored procedures.

I did a bunch of research and found out that if I added a commented out "sp_password" command in my stored procedure, then the stored procedure call gets masked from the profiler. This seem to have been true with SQL Server 2000 but is not true for SQL Server 2005.

Does anyone know of a method to mark a stored procedure -- or a parameter to a stored procedure -- as masked so that either the call or the parameter value are masked ?

Thank You,

Tarin

An alternative could be using a symmetric key protected by a password, as long as your application generates the CREATE SYMMETRIC KEY and OPEN SYMMETRIC KEY statements directly instead of calling them inside a SP (otherwise the password will still be passed as a parameter, and will be in clear in the profiler).

-- Profiler will display

-- --*CREATE SYMMETRIC KEY--

CREATE SYMMETRIC KEY key01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD = '&0m3 p4xw0Rd'

go

CREATE TABLE t_demo( data varbinary(100) )

go

-- Profiler:

-- --*OPEN SYMMETRIC KEY-

OPEN SYMMETRIC KEY key01 DECRYPTION BY PASSWORD = '&0m3 p4xw0Rd'

go

-- The password is not needed naymore,

-- The key will remain open in this session until CLOSE SYMMETRIC KEY is called

INSERT INTO t_demo VALUES( EncryptByKey( key_guid('key01'), 'My Secret' ))

go

SELECT convert(varchar(100), DecryptByKey( data )) as 'plaintext' FROM t_demo

go

CLOSE SYMMETRIC KEY key01

Go

The masking of the profiler by adding “sp_password” was actually a bug in SQL Server 2000. I have opened a suggestion to add a mechanism to mark modules as sensitive in order to mask them from the profiler.

Thanks a lot for your feedback,

-Raul Garcia

SDE/T

SQL Server Engine

Encrypt data

Is there a howTo article that explains how to encrypt the data between the
server and client application. I work at a medical facility and this is a
HIPPA requirement.I am using SQL200 sp4
"Joe McHae" wrote:

> Is there a howTo article that explains how to encrypt the data between the
> server and client application. I work at a medical facility and this is a
> HIPPA requirement.|||You can use the .NET System.Security.Cryptography namespace fucntions to
encrypt data at the client. Google System.Security.Cryptography for
samples.
You can use SSL to secure data in-transit between the client and server.
Google SSL SQL Server 2000 for setup and configuration.
You can use extended procedures to encrypt data at the server. Look here
for examples:
http://www.sqlservercentral.com/col...oolkitpart1.asp
"Joe McHale" <JoeMcHale@.discussions.microsoft.com> wrote in message
news:4CD0483C-E188-4C7A-9B62-89B2A4E394E7@.microsoft.com...[vbcol=seagreen]
>I am using SQL200 sp4
> "Joe McHae" wrote:
>

Encrption Level

Does anyone know what encryption level (algorithm, API, strength, etc.) is used when SSAS 2005 encrypts data between a client and the server?

Thanks,
Dave Fackler1. For native connections (when you connect directly to AS2005, no http/IIS) SSPI is used for authentication, signatures and encryption and these 2 connection string properties can be used:

Protection Level =
NONE (no authentication, no signatures, no encryption)
CONNECT (authentication, clear text without signatures)
PKT INTEGRITY (authentication, clear text with signatures - for replay and data tempering protection)
PKT PRIVACY (authentication, encryption, signatures included)

SSPI = the name of the SSPI package to be used, for example 'Kerberos' or 'NTLM' (default is 'Negociate')

2. For http connections, you can use https

3. For local cubes connections, the problem won't apply

Adrian Dumitrascu.|||Do you know how can we change the protection level to NONE on a deployed OLAP project?

Encrption Level

Does anyone know what encryption level (algorithm, API, strength, etc.) is used when SSAS 2005 encrypts data between a client and the server?

Thanks,
Dave Fackler1. For native connections (when you connect directly to AS2005, no http/IIS) SSPI is used for authentication, signatures and encryption and these 2 connection string properties can be used:

Protection Level =
NONE (no authentication, no signatures, no encryption)
CONNECT (authentication, clear text without signatures)
PKT INTEGRITY (authentication, clear text with signatures - for replay and data tempering protection)
PKT PRIVACY (authentication, encryption, signatures included)

SSPI = the name of the SSPI package to be used, for example 'Kerberos' or 'NTLM' (default is 'Negociate')

2. For http connections, you can use https

3. For local cubes connections, the problem won't apply

Adrian Dumitrascu.|||Do you know how can we change the protection level to NONE on a deployed OLAP project?

Encrption Level

Does anyone know what encryption level (algorithm, API, strength, etc.) is used when SSAS 2005 encrypts data between a client and the server?

Thanks,
Dave Fackler1. For native connections (when you connect directly to AS2005, no http/IIS) SSPI is used for authentication, signatures and encryption and these 2 connection string properties can be used:

Protection Level =
NONE (no authentication, no signatures, no encryption)
CONNECT (authentication, clear text without signatures)
PKT INTEGRITY (authentication, clear text with signatures - for replay and data tempering protection)
PKT PRIVACY (authentication, encryption, signatures included)

SSPI = the name of the SSPI package to be used, for example 'Kerberos' or 'NTLM' (default is 'Negociate')

2. For http connections, you can use https

3. For local cubes connections, the problem won't apply

Adrian Dumitrascu.|||Do you know how can we change the protection level to NONE on a deployed OLAP project?