Empty Column: encryption

Showing posts with label encryption. Show all posts

Thursday, March 29, 2012

encryption--the sequel

Hello:
Earlier this week, I had posed a question on encryption and certificates. A
very nice person gave me two blogs to read. I appreciated that and it was
very helpful. Now, I just want to make sure that I have the order down pat.
Could someone please review the order of data that I have below and let me
know if I have this right? Especially, please let me know if I am correct i
n
the order on number 5 and number 6. Thanks!!! Here's my list for your
review:
--create a master key
--create a certificate
--create a symmetric key
--create a stored procedure that encrypts the data with the certificate
--grant the ALTER ANY SYMMETRIC KEY permissions the the user
--create two stored procedures to use the certificate to open the symmetric
key
childofthe1980sHi
Laurentiu's blog gives you everything you need to know about this.
If you want to give a user access to the keys then you can do the following,
this is taken by stripping out the actions for Doc1 in
http://blogs.msdn.com/lcris/archive.../16/504692.aspx :
Create database master key (if not already done so)
Create certificate to encrypt symmetric key with authorisation to userX
Create symmetric key encrypted by certificate
Grant view definition permissions to view key by userX
Create procedure to open symmetric key and encrypt/decrypt using symmetric k
ey
Grant permissions to execute procedure to userX
If you want to restrict the user to only decrypt or encrypt then you can
sign the procedure with a second certificate see
http://blogs.msdn.com/lcris/archive.../13/512829.aspx :
Create database master key (if not already done so)
Create first certificate to encrypt symmetric key
Create symmetric key encrypted by first certificate
Create procedure to open symmetric key and encrypt/decrypt using symmetric k
ey
Create second certificate to sign code
Create user mapped to the second certificate
Grant view definition permission to first symmetric key to second certificat
e
Grant control on first certificate to second certificate
Sign (add signature) the procedure with second certificate
Grant permissions to execute procedure to userX
John
"childofthe1980s" wrote:

> Hello:
> Earlier this week, I had posed a question on encryption and certificates.
A
> very nice person gave me two blogs to read. I appreciated that and it was
> very helpful. Now, I just want to make sure that I have the order down pa
t.
> Could someone please review the order of data that I have below and let me
> know if I have this right? Especially, please let me know if I am correct
in
> the order on number 5 and number 6. Thanks!!! Here's my list for your
> review:
> --create a master key
> --create a certificate
> --create a symmetric key
> --create a stored procedure that encrypts the data with the certificate
> --grant the ALTER ANY SYMMETRIC KEY permissions the the user
> --create two stored procedures to use the certificate to open the symmetr
ic
> key
> childofthe1980ssql

encryption--the sequel

Hello:
Earlier this week, I had posed a question on encryption and certificates. A
very nice person gave me two blogs to read. I appreciated that and it was
very helpful. Now, I just want to make sure that I have the order down pat.
Could someone please review the order of data that I have below and let me
know if I have this right? Especially, please let me know if I am correct in
the order on number 5 and number 6. Thanks!!! Here's my list for your
review:
--create a master key
--create a certificate
--create a symmetric key
--create a stored procedure that encrypts the data with the certificate
--grant the ALTER ANY SYMMETRIC KEY permissions the the user
--create two stored procedures to use the certificate to open the symmetric
key
childofthe1980sHi
Laurentiu's blog gives you everything you need to know about this.
If you want to give a user access to the keys then you can do the following,
this is taken by stripping out the actions for Doc1 in
http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx :
Create database master key (if not already done so)
Create certificate to encrypt symmetric key with authorisation to userX
Create symmetric key encrypted by certificate
Grant view definition permissions to view key by userX
Create procedure to open symmetric key and encrypt/decrypt using symmetric key
Grant permissions to execute procedure to userX
If you want to restrict the user to only decrypt or encrypt then you can
sign the procedure with a second certificate see
http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx :
Create database master key (if not already done so)
Create first certificate to encrypt symmetric key
Create symmetric key encrypted by first certificate
Create procedure to open symmetric key and encrypt/decrypt using symmetric key
Create second certificate to sign code
Create user mapped to the second certificate
Grant view definition permission to first symmetric key to second certificate
Grant control on first certificate to second certificate
Sign (add signature) the procedure with second certificate
Grant permissions to execute procedure to userX
John
"childofthe1980s" wrote:
> Hello:
> Earlier this week, I had posed a question on encryption and certificates. A
> very nice person gave me two blogs to read. I appreciated that and it was
> very helpful. Now, I just want to make sure that I have the order down pat.
> Could someone please review the order of data that I have below and let me
> know if I have this right? Especially, please let me know if I am correct in
> the order on number 5 and number 6. Thanks!!! Here's my list for your
> review:
> --create a master key
> --create a certificate
> --create a symmetric key
> --create a stored procedure that encrypts the data with the certificate
> --grant the ALTER ANY SYMMETRIC KEY permissions the the user
> --create two stored procedures to use the certificate to open the symmetric
> key
> childofthe1980s

encryption--the sequel

Hello:
Earlier this week, I had posed a question on encryption and certificates. A
very nice person gave me two blogs to read. I appreciated that and it was
very helpful. Now, I just want to make sure that I have the order down pat.
Could someone please review the order of data that I have below and let me
know if I have this right? Especially, please let me know if I am correct in
the order on number 5 and number 6. Thanks!!! Here's my list for your
review:
--create a master key
--create a certificate
--create a symmetric key
--create a stored procedure that encrypts the data with the certificate
--grant the ALTER ANY SYMMETRIC KEY permissions the the user
--create two stored procedures to use the certificate to open the symmetric
key
childofthe1980s
Hi
Laurentiu's blog gives you everything you need to know about this.
If you want to give a user access to the keys then you can do the following,
this is taken by stripping out the actions for Doc1 in
http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx :
Create database master key (if not already done so)
Create certificate to encrypt symmetric key with authorisation to userX
Create symmetric key encrypted by certificate
Grant view definition permissions to view key by userX
Create procedure to open symmetric key and encrypt/decrypt using symmetric key
Grant permissions to execute procedure to userX
If you want to restrict the user to only decrypt or encrypt then you can
sign the procedure with a second certificate see
http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx :
Create database master key (if not already done so)
Create first certificate to encrypt symmetric key
Create symmetric key encrypted by first certificate
Create procedure to open symmetric key and encrypt/decrypt using symmetric key
Create second certificate to sign code
Create user mapped to the second certificate
Grant view definition permission to first symmetric key to second certificate
Grant control on first certificate to second certificate
Sign (add signature) the procedure with second certificate
Grant permissions to execute procedure to userX
John
"childofthe1980s" wrote:

> Hello:
> Earlier this week, I had posed a question on encryption and certificates. A
> very nice person gave me two blogs to read. I appreciated that and it was
> very helpful. Now, I just want to make sure that I have the order down pat.
> Could someone please review the order of data that I have below and let me
> know if I have this right? Especially, please let me know if I am correct in
> the order on number 5 and number 6. Thanks!!! Here's my list for your
> review:
> --create a master key
> --create a certificate
> --create a symmetric key
> --create a stored procedure that encrypts the data with the certificate
> --grant the ALTER ANY SYMMETRIC KEY permissions the the user
> --create two stored procedures to use the certificate to open the symmetric
> key
> childofthe1980s

Tuesday, March 27, 2012

Encryption; SQL Server 2005 & Windows 2003 Server

Any further input would be appreciated ...
Pro EFS:
Indexs, Primary Keys, Foreign Keys, DEFAULTS, CHECK CONSTRAINTS are preserve
d.
Databases modifications need not consider Encryption.
Patterns & Practices
http://msdn.microsoft.com/library/d...
h05.asp
http://msdn.microsoft.com/library/d...r />
MCh18.asp
http://msdn.microsoft.com/library/d.../>
SecDBSe.asp
Other Technical Articles
http://www.microsoft.com/technet/pr...0.mspx?mfr=true
http://www.microsoft.com/technet/pr...fr=
true
http://www.microsoft.com/technet/ar...n/sp3sec02.mspx
http://www.microsoft.com/technet/pr...n/sp3sec04.mspx
http://www.microsoft.com/technet/pr...y/sqlorcle.mspx
http://www.microsoft.com/technet/se...phyetc/efs.mspx
http://www.sqlservercentral.com/col...menting_efs.asp
http://www.microsoft.com/technet/pr...5/multisec.mspx
http://www.akadia.com/services/sqls...l#_Toc513865376
http://www.sans.org/top20/2002/mssql_checklist.pdf
Case Study
http://www.microsoft.com/canada/cas...worksafebc.mspx
Anti-EFS:
1. If the file is not created in an Encrypted Directory the temporary fil
e
created by EFS during encryption remains in clear-text and is
vulnerable.
a) cipher.exe /W must be used to Wipe the temporary file.
2. EFS will not function in a Clustered Environment.
3. If the Server crashes when an Encrypted File is open the pagefile.sys
will
contain vulnerable clear-text of the Encrypted File on restart.
4. The Windows Administrator(s) can "Set Password ..." of the Key Owner
and the Key Owner will not be able to access the data.
5. If the Key Owner does not specify a Data Recovery Agent (DRA) AND does
not backup the PKI the data might become inaccessible under
circumstances
such as "4." above.
6. Encrypted Files cannot be backed up to non-NTFS devices except with
Windows
Backup utilities.
7. Extra steps must be taken over-and-above conventional SQL Server Backu
p,
Recovery and Disaster Recovery procedures.
8. The Windows Administrator can access the (otherwise) encrypted data if
SQL Server "BUILTIN\Administrators" is not removed.
9. The Database *.mdf & *.ldf files cannot be moved between domains and
retain
the Encrypted Attribute.
10. Stealing a local account password is easy using common hacker tools in
standalone mode.
11. Encrypted files stored on file servers are decrypted on the server
and then
transported in clear text across the network to the user's workstation.
Because EFS needs access to the user's private key, which is held in
the
profile, the server must be "trusted for delegation" and have access to
the user's local profile.
a) Requires IPSec to secure the file transfer between file server
and
user machine.
12. "The EnCase EFS Module provides Encrypting File System (EFS) folder a
nd
file decryption capabilities, for locally authenticated users."
(http://www.digitalintelligence.com/...oftware/encase/)"ITContractor" <ITContractor@.discussions.microsoft.com> wrote in message
news:16785415-7A45-4031-A599-86896233DC15@.microsoft.com...
> Any further input would be appreciated ...
> Pro EFS:
>
. . .
> 8. The Windows Administrator can access the (otherwise) encrypted data
> if
> SQL Server "BUILTIN\Administrators" is not removed.
Although this is true, removing the BUILTIN\Administrators account is no
protection against a Windows admin. A windows admin on the box can shut
down SQL Server, replace the Master database, restart and attach your
encrypted database. Or just restart the instance in single-user mode.
David

Encryption; SQL Server 2005 & Windows 2003 Server

Issue(s):
1. We *must* encrypt all data items in a Database.
2. SQL Server 2005 Encryption places a tremendous burden on the
system as a whole when many columns are encrypted, especially
columns involved in indexes. Response time(s) are unbearable.
Proposed Solution:
A. Create/Encrypt the *.mdf & *.ldf files as Windows 2003 Server
Encrypted files thereby using the CrytoAPI at the file-level rather
than through SQL Server wrappers etc.
B. As the Key Owner/Windows User of the Encrypted files of "A."
above, attach to the files in SQL Server 2005/2000.
C. The availability of individual Column Privileges is handled by
conventional SQL Server privileges.
Observations (based on actual Testing):
I. The Database application runs fine and response times are
very good.
II. Database changes can be made as though no encryption were
done. This includes the creation of ad hoc tables etc.
III. Current an furture Applications need not be modified to OPEN
SYMMETRIC KEY(s) or access hashed values to speed Queries.
QUESTIONS:
* Has Microsoft tested this approach to encrypting the entire
Database ?
* Is there any collateral experience or other Developers who have
such a configuration in Production ?
* Are there any foreseeable issues with this approach?
Thank you (in advance)I am using SQL2000 and SQL2005 and have all database files on an encrypted
partion. Encryption is done with TrueCrypt and i never had any problem
with this solution.
But that is only for securing the data if somebody will steal the computer.
It does not help if somebody hacks the database server. For this you need
the built in encryption from SQL-Server, where you need the password in the
query, even if you logged in as admin and have all rights.
With built in encrytion it is possible, that two persons with different
passphrases see different parts of the data, even if they use the same
query on the same column.
So encryption of the filesystem and encryption of data in the database are
different things.
bye, Helmut|||Thank you Helmut.
Our primary Data Security concerns are, indeed:
* intentional theft of the System(s) or, specifically, the Hard
Drives.
* unintentional loss/natural breach of physical security
e.g. earthquake, hurricane, terrorism ...
We are unconcerned about :
* the Database Administrator (in the serveradmin role) being
able to view unencrypted data.
* having many levels of granularity of Security within a given
Database where different Users/Roles see differing sets of
Confidential Data depending on their particular need-to-know.
We prefer to use current Vendors for the solution since the Client
has a rather complex and time-consuming procurement process.
Regards ...
"Helmut Woess" wrote:

> I am using SQL2000 and SQL2005 and have all database files on an encrypted
> partion. Encryption is done with TrueCrypt and i never had any problem
> with this solution.
> But that is only for securing the data if somebody will steal the computer
.
> It does not help if somebody hacks the database server. For this you need
> the built in encryption from SQL-Server, where you need the password in th
e
> query, even if you logged in as admin and have all rights.
> With built in encrytion it is possible, that two persons with different
> passphrases see different parts of the data, even if they use the same
> query on the same column.
> So encryption of the filesystem and encryption of data in the database are
> different things.
> bye, Helmut
>|||"ITContractor" <ITContractor@.discussions.microsoft.com> wrote in message
news:EA318A7B-AD03-4E9C-891E-495EE1BA0B49@.microsoft.com...
> Issue(s):
> 1. We *must* encrypt all data items in a Database.
> 2. SQL Server 2005 Encryption places a tremendous burden on the
> system as a whole when many columns are encrypted, especially
> columns involved in indexes. Response time(s) are unbearable.
> Proposed Solution:
> A. Create/Encrypt the *.mdf & *.ldf files as Windows 2003 Server
> Encrypted files thereby using the CrytoAPI at the file-level rather
> than through SQL Server wrappers etc.
> B. As the Key Owner/Windows User of the Encrypted files of "A."
> above, attach to the files in SQL Server 2005/2000.
> C. The availability of individual Column Privileges is handled by
> conventional SQL Server privileges.
> Observations (based on actual Testing):
> I. The Database application runs fine and response times are
> very good.
> II. Database changes can be made as though no encryption were
> done. This includes the creation of ad hoc tables etc.
> III. Current an furture Applications need not be modified to OPEN
> SYMMETRIC KEY(s) or access hashed values to speed Queries.
> QUESTIONS:
> * Has Microsoft tested this approach to encrypting the entire
> Database ?
Yes. Putting SQL Databases on an Encrypted File System is supported.

> * Is there any collateral experience or other Developers who have
> such a configuration in Production ?
> * Are there any foreseeable issues with this approach?
>
Expect physical IO performance to be really bad. Plan around that by
providing plenty of RAM and load-testing your application.
David|||Thank you David,
I find your response incredibly interesting as I assume your corporation
has some first-hand experience as well as feedback from Microsoft Corp.
with the approach I am describing. My bosses are a big City Department
and have many Applications' Databases they would like to protect
against accidental/intentional exposure e.g. System/HDD theft or
loss or physical exposure due to natural or other unsavory causes.
At the risk of over-stepping my bounds, any further information you are
at liberty to share as:
1. Types of applications (high xactoin telecomm, DSS, low xactoin
record-keeping) 2. DB Size (our DB's will be 3Gb to 600Gb depending on the
App.)
3. Production environment (SAN, Cluster, Stand-alone)
In any event, many thanks for taking the time to respond.
========================================
===========
> "ITContractor" <ITContractor@.discussions.microsoft.com> wrote in message
> news:EA318A7B-AD03-4E9C-891E-495EE1BA0B49@.microsoft.com...
> Yes. Putting SQL Databases on an Encrypted File System is supported.
>
> Expect physical IO performance to be really bad. Plan around that by
> providing plenty of RAM and load-testing your application.
> David

Encryption with SSAS 2005

Is there a way to encrypt data in SSAS in a similar fashion to the built in encryption provided for a relational database in SQL Server 2005 (SMK to encrypt the DMK, which can then be used to create sym/asym keys to encrypt specific columns)?

If so, how do I create a DSV in SSAS that is based on a table(s) which has encrypted data? Also, how do I encrypt specific fact tables in my cube in SSAS?

Analysis Services does not support encryption of it's data.

As for creating cubes based on the encrypted data within SQL Server, that should not be different from any other application reading encypted data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

encryption with certificate

I am trying to create a encrypted row in my database
Everything here worked except that when i run the final query to decrypt the data
It just comes up with null for each row. Even if i do a query to show me the rows that are not null
It's like it is saying yeah there is data here but I am only going to show you null instead of what I am supposed to decrypt.
Here is what I tried from start to finish

Create Certificate TestCertEncryptionBy Password ='Password'With Subject ='SQLCert',Expiry_Date ='12/01/2050';declare @.Testnvarchar(50)set @.Test='123456789'insert into testenc (testencry)Values (encryptbyCert(Cert_ID('TestCert'),@.Test ))selectconvert (Nvarchar(50),DecryptByCert(Cert_ID('TestCert'),testencry,N'Password'))As Testfrom testenc

I am using sql 2005 by the way|||Nevermind Just realized I need to use VarBinary instead of NvarChar to store the data!

Encryption with Analysis Services 2005

As I understand it SSAS encrypts its data by default; however, I'm looking for strategies on encrypting data in the underlying datawarehouse. If you have a column encrypted in the datawarehouse, what are the options to expose that data, selectively of course, through Analysis Services? The only solution I've found is to bind a dimension to a view in the datasource and have that view decrypt the column. The dimension attribute could then be selectively exposed based on the role(s) the user has access to.

Is this the BEST way to do it? Are there other options and considerations? Are there any great whitepapers on this subject? I haven't found any myself.

Thanks in advance,

Terry

Havent seen any whitepapers on the subject.

Another idea for you to consider is to create a named query in Analysis Services DSV. See if you can use that to skip creating a view in relational database.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I am working with SQL server 2005 analysis and reporting services. I am

instructed to create a cube for a database using analysis services and
then replicate it so as to produce reports online by reporting services

when requested by clients. I am able to create the cube and also deploy

the report made, in HTTP separately. But the following doubts arise
during the Cube deployment:

The Cube was created as per the requirements by my team lead. Then I
used the deployment wizard in the analysis services 2005 to convert it
into XML script. Using the SQL Management Studio I opened it as
Analysis Server scripts and used the local host as the system in the
connections windows then loaded the XML script into the Queries window
as a XMLA query and executed it. What has to be done after this in
order to use it as a production server?

Once the production server is setup and the connection is made with the

staging server, How can we set the timings to when the updating of the
production server has to be set in terms of hours, days or weeks?

Hoping these doubts would be clarified as early as possible.

Encryption Varbinary Length

Using the new encryption included in SQL Server 2005, what is a good way to determine what length I should use for the column?

For example, I am encrypting a column, its maxlength is about 30 characters, but when encrypted, the encrypted value extends from between 50 and no more than 68 characters-

So if I had a column with a max of 500 or so characters, how could I know what varbinary length I should set it to if I were to encrypt it, without actually finding the highest value I could possibly fit into the field?

Is it good practice to just make it a varbinary(max) field?

-rob

My recommendation would be to create a dummy value of the maximum possible size (for example, on a varchar(500), create a 500 characters strings) and encrypt it with the same key and parameters you would normally use. That way you know you have reserved enough space to accept the maximum plaintext value your application accepts.

Example:

CREATE SYMMETRIC KEY key_demo WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = 'My 53Cr3+ p@.zzw0rD'

OPEN SYMMETRIC KEY key_demo

DECRYPTION BY PASSWORD = 'My 53Cr3+ p@.zzw0rD'

DECLARE @.MaxPlaintext nvarchar(500)

DECLARE @.Ciphertext varbinary(8000)

SET @.MaxPlaintext = replicate (N'a', 500)

-- Notice that I am using Unicode and the length is in bytes = 1000

PRINT datalength( @.MaxPlaintext )

-- Using the basic encryption parameters

SET @.Ciphertext = EncryptByKey( key_guid('key_demo'), @.MaxPlaintext )

-- Length using default parameters

PRINT datalength( @.Ciphertext )

-- Using the optional parameters

SET @.Ciphertext = EncryptByKey( key_guid('key_demo'), @.MaxPlaintext, 1, 'Dummy value' )

-- Length using optional parameters

PRINT datalength( @.Ciphertext )

I wrote an article (SQL Server 2005 Encryption – Encryption and data length limitations, http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx) that explains a little bit more about the encryption length limitations in SQL Server 2005. In that article I have a formula that may also help, but I would recommend using the method shown above instead of the formula in the article.

I hope I was able to help you. Please let us know if you have any further questions or feedback.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||This example will work for my needs - I should be able to get away with that as it is not often that a user will use the full length of a column. Thanks for validating.|||Raul, thanks for the solution on encrypting large amount of data. We have tried your approach on some BLOBs (e.g. files stored as binaries). The performance is very dismal though it works. Downloading a 3MB file that's encrypted took more than 45 seconds (the same file without encryption can be downloaded from the same webpage in 5 seconds). Any suggestions? I understand that SQL Server is not an efficient way to store files to begin with but we have to use it due to data center restrictions.|||

Hey Bob,

A lot of factors will impact the performance. Given the dramatic difference in times, I'm wondering if this is actually caused by the query you are using the retrieve the data? Are you only decrypting the blob or are other columns in the table encrypted as well?

I will run some tests on this, but if it is possible can you share the queries you are using with us? Or at least sample queries which approximate how you are retrieving the data?

Thanks,

Sung

|||

Sung:

There is only one field in the DB that's encrypted, the one containing the BLOB. The query is pretty much a copy of what Raul posted on his blog:

--
<edited to remove the source code sample>

|||

Unfortunately the work around I give is not very efficient with data that is larger than a few segments.

The main problem with this workaround (in terms of efficiency) is that the overhead SQL Server needs to pay besides the direct encryption/decryption (lookup for key in key ring, initialize key in cryptographic provider, verify inner header for consistency, etc.) will hit you multiple times for each blob. In the sample you mentioned of a 3MB file, you will be affected by this overhead more than 380 times.

Given the length and nature of the data you are manipulating, I would suggest using a CLR UDF instead and encrypt using CLR cryptographic APIs. The performance should be much better and also much more efficient in terms of space needed (not to mention a much cleaner code than my workaround).

I hope this information will help. Thanks a lot for your feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul, thanks for the reply. would a CLR UDF approach still use the SQL 2005's key/certificate infrstructure or it's completely .NET solution with separate keys? I have done file encryptions in .NET using X509 cert and symmetric keys, which are pretty easy to implement, but I'd like to leverage the keys created in SQL 2005 so the key management is consistent with other encrypted data in storage.

In addition, if you could show me some examples of using CLR UDF for SQL encryption that'd be great.

Thanks again

|||

Actually I was thinking of doing the whole key management and encryption directly in CLR due to the huge overhead you would have to pay for using the SQL Server infrastructure for large objects.

Another solution I can think of using SQL Server key management infrastructure would be to split the original plaintext in multiple rows and leverage on multiple client connections to encrypt/decrypt in parallel. For example: Create a table that will store the ciphertext as fragments in multiple rows (i.e. file_id, file_fragment_id used as primary key); your client can split the plaintext in segments (you could use a fixed length for the segments to make the file SEEK easier, let’s say 7K per segment) and using multiple connections send in parallel encrypt and insert requests to SQL Server (the fragment_id will be relative to the offset of the plaintext). As the fragments will be encrypted independently, they can also be decrypted independently and assembled again in the client application after decryption.

Hope this idea will work for you, or at least give you some other ideas.

-Raul Garcia

SDE/T

SQL Server Engine

Encryption using MS SQL 2005

Hello,

I have a application server with about 500,000 users. We are trying to tacle the issue of encryption. We are using MS SQL 2005 and I am sure that symmetric encryption would be the best, due to speed. But heres the kicker.....We want the whole database encrypted at rest, and when clients log onto our ASP to gain access to their programms the data must be in plain text. Any sugesstions?

Thanks,

Corliss

You have to choose: either the database is encrypted, or it is not.

If it is encrypted, then each query will have to decrypt the data upon access.

OR, you may choose to use SSL or IPSEC to control and encrypt the data at the transport layer. (But that does not encrypt the database 'at rest'.)

I suggest that you look in Books Online for the topics: Encryption, Encrypted data. Here are some resources about encryption:

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

Encryption -Column-level using the CryptoAPI
http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp

Encryption -Example
http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx

Encryption/Decryption
http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp
http://blogs.msdn.com/lcris/archive/category/10357.aspx
http://blogs.msdn.com/sqlblog/archive/2006/11/02/part-i-data-security-enhancements-in-sql-server-2005.aspx

|||

Corliss, do you also expect that data is accessible only if clients connect via your ASP application?

Right now, there isn't any such capability, but we're always welcoming suggestions for new features, so we're interested in finding more about what type of encryption feature you would find helpful.

Thanks
Laurentiu

|||

Cristofor,

That woulbe be Ideal, but I also know that it isn't possible. I want to encrypt the entire database and only allow data to be accessible through the applications that we have on the server. this is troulesome for sure because the SQL server works best when authenitcation of users/ groups takes place.

|||Look into using IPSEC. It is not a 'complete' solution for your problem, but perhaps it can narrow down the attack vectors and reduce the risk.|||

Using IPSEC?

In terms of network traffic I don't really need to. All traffic is encrypted due to citrix and a VPN. "At Rest" is the issue:)

Is there a way to decrypt when the data needs to be accessed...like when an odbc connection is made.

|||

At this moment, there isn't any feature that would answer your requirements. The SQL Server 2005 encryption is meant for selective encryption of data - encrypting the entire database would require a different solution.

For the access restricted to applications, this is again not something that can be guaranteed - users can always bypass applications and connect directly by figuring out how the application connects in the first place. Encryption wouldn't address this scenario.

Thanks
Laurentiu

|||

You might find this thread 'insightful'. Similar issue.

http://forums.microsoft.com/MSDN/showpost.aspx?postid=1354173&siteid=1

|||

Corliss,

We have plans to release a feature that may solve your exact requirements in the next version of SQL Server. However, this may be limited to only certain SKUs. What SKU does your organization currently have license to? Would you be interested in participating with us in a public CTP program to try it out?

Thanks

Andy

|||

please forward me more details

corliss

Encryption using MS SQL 2005

Hello,

Thanks,

Corliss

You have to choose: either the database is encrypted, or it is not.

If it is encrypted, then each query will have to decrypt the data upon access.

OR, you may choose to use SSL or IPSEC to control and encrypt the data at the transport layer. (But that does not encrypt the database 'at rest'.)

I suggest that you look in Books Online for the topics: Encryption, Encrypted data. Here are some resources about encryption:

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

Encryption -Column-level using the CryptoAPI
http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp

Encryption -Example
http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx

|||

Corliss, do you also expect that data is accessible only if clients connect via your ASP application?

Right now, there isn't any such capability, but we're always welcoming suggestions for new features, so we're interested in finding more about what type of encryption feature you would find helpful.

Thanks
Laurentiu

|||

Cristofor,

|||Look into using IPSEC. It is not a 'complete' solution for your problem, but perhaps it can narrow down the attack vectors and reduce the risk.|||

Using IPSEC?

In terms of network traffic I don't really need to. All traffic is encrypted due to citrix and a VPN. "At Rest" is the issue:)

Is there a way to decrypt when the data needs to be accessed...like when an odbc connection is made.

|||

Thanks
Laurentiu

|||

You might find this thread 'insightful'. Similar issue.

http://forums.microsoft.com/MSDN/showpost.aspx?postid=1354173&siteid=1

|||

Corliss,

Thanks

Andy

|||

please forward me more details

corliss

sql

Encryption Standards

I have a general question on data encryption.
We need to store encrypted creditcard info in a SQL Server 2000 database.
The encryption method needs to meet the AES standard.
Does anyone know if a value encypted under the AES standard will retain its
data length?
In other words, if I have a 15 character credit card number like...
123456789012345
...will it still be 15 characters in length when it is encrypted like...
shj)k2&bs&_yqE#
...or does the AES standard require something other than a character by
character encryption so I end up with a value that is more than 15 characters
like..
/Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
I need to know if I have to expand my column width and possibly change code
to accomodate the encryption.
If anyone has any experience with this, I would appreciate their insight.
Thanks
Dave,
Might want to ask the third-party vendor directly. Might try here:
http://www.activecrypt.com/products.html
HTH
Jerry
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:883F0E2A-8294-47E9-AE62-A6EE59791618@.microsoft.com...
>I have a general question on data encryption.
> We need to store encrypted creditcard info in a SQL Server 2000 database.
> The encryption method needs to meet the AES standard.
> Does anyone know if a value encypted under the AES standard will retain
> its
> data length?
> In other words, if I have a 15 character credit card number like...
> 123456789012345
> ...will it still be 15 characters in length when it is encrypted like...
> shj)k2&bs&_yqE#
> ..or does the AES standard require something other than a character by
> character encryption so I end up with a value that is more than 15
> characters
> like..
> /Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
> I need to know if I have to expand my column width and possibly change
> code
> to accomodate the encryption.
> If anyone has any experience with this, I would appreciate their insight.
> Thanks
|||You'll need to change your VARCHAR column to BINARY or VARBINARY unless
you are going to implement some character set encoding as well as
encryption.
The AES block size is 128 bits so you'll need at least one extra byte.
Depending on the cipher mode you will also need an additional 128 bit
initialization vector.
Jerry has it right though. Ask the vendor or whoever will implement the
encryption.
David Portas
SQL Server MVP
|||Thanks guys.
Yes I am experimenting with "Ivy Encryption" and whatever value it encrypts
is expanded by a factor of 2.75.
I was just wondering if I should expect this from all AES encryption
schemes.
I don''t think it would meet the standard if it each individual character
were encrypted to a single character. If anyone could confirm I would be
grateful.

Encryption Standards

I have a general question on data encryption.
We need to store encrypted creditcard info in a SQL Server 2000 database.
The encryption method needs to meet the AES standard.
Does anyone know if a value encypted under the AES standard will retain its
data length?
In other words, if I have a 15 character credit card number like...
123456789012345
...will it still be 15 characters in length when it is encrypted like...
shj)k2&bs&_yqE#
..or does the AES standard require something other than a character by
character encryption so I end up with a value that is more than 15 character
s
like..
/Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
I need to know if I have to expand my column width and possibly change code
to accomodate the encryption.
If anyone has any experience with this, I would appreciate their insight.
ThanksDave,
Might want to ask the third-party vendor directly. Might try here:
http://www.activecrypt.com/products.html
HTH
Jerry
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:883F0E2A-8294-47E9-AE62-A6EE59791618@.microsoft.com...
>I have a general question on data encryption.
> We need to store encrypted creditcard info in a SQL Server 2000 database.
> The encryption method needs to meet the AES standard.
> Does anyone know if a value encypted under the AES standard will retain
> its
> data length?
> In other words, if I have a 15 character credit card number like...
> 123456789012345
> ...will it still be 15 characters in length when it is encrypted like...
> shj)k2&bs&_yqE#
> ..or does the AES standard require something other than a character by
> character encryption so I end up with a value that is more than 15
> characters
> like..
> /Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
> I need to know if I have to expand my column width and possibly change
> code
> to accomodate the encryption.
> If anyone has any experience with this, I would appreciate their insight.
> Thanks|||You'll need to change your VARCHAR column to BINARY or VARBINARY unless
you are going to implement some character set encoding as well as
encryption.
The AES block size is 128 bits so you'll need at least one extra byte.
Depending on the cipher mode you will also need an additional 128 bit
initialization vector.
Jerry has it right though. Ask the vendor or whoever will implement the
encryption.
David Portas
SQL Server MVP
--|||Thanks guys.
Yes I am experimenting with "Ivy Encryption" and whatever value it encrypts
is expanded by a factor of 2.75.
I was just wondering if I should expect this from all AES encryption
schemes.
I don''t think it would meet the standard if it each individual character
were encrypted to a single character. If anyone could confirm I would be
grateful.

Encryption Standards

I have a general question on data encryption.
We need to store encrypted creditcard info in a SQL Server 2000 database.
The encryption method needs to meet the AES standard.
Does anyone know if a value encypted under the AES standard will retain its
data length?
In other words, if I have a 15 character credit card number like...
123456789012345
...will it still be 15 characters in length when it is encrypted like...
shj)k2&bs&_yqE#
..or does the AES standard require something other than a character by
character encryption so I end up with a value that is more than 15 characters
like..
/Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
I need to know if I have to expand my column width and possibly change code
to accomodate the encryption.
If anyone has any experience with this, I would appreciate their insight.
ThanksDave,
Might want to ask the third-party vendor directly. Might try here:
http://www.activecrypt.com/products.html
HTH
Jerry
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:883F0E2A-8294-47E9-AE62-A6EE59791618@.microsoft.com...
>I have a general question on data encryption.
> We need to store encrypted creditcard info in a SQL Server 2000 database.
> The encryption method needs to meet the AES standard.
> Does anyone know if a value encypted under the AES standard will retain
> its
> data length?
> In other words, if I have a 15 character credit card number like...
> 123456789012345
> ...will it still be 15 characters in length when it is encrypted like...
> shj)k2&bs&_yqE#
> ..or does the AES standard require something other than a character by
> character encryption so I end up with a value that is more than 15
> characters
> like..
> /Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
> I need to know if I have to expand my column width and possibly change
> code
> to accomodate the encryption.
> If anyone has any experience with this, I would appreciate their insight.
> Thanks|||You'll need to change your VARCHAR column to BINARY or VARBINARY unless
you are going to implement some character set encoding as well as
encryption.
The AES block size is 128 bits so you'll need at least one extra byte.
Depending on the cipher mode you will also need an additional 128 bit
initialization vector.
Jerry has it right though. Ask the vendor or whoever will implement the
encryption.
--
David Portas
SQL Server MVP
--|||Thanks guys.
Yes I am experimenting with "Ivy Encryption" and whatever value it encrypts
is expanded by a factor of 2.75.
I was just wondering if I should expect this from all AES encryption
schemes.
I don''t think it would meet the standard if it each individual character
were encrypted to a single character. If anyone could confirm I would be
grateful.

Encryption SQL 2005

I have a desire to encrypt an entire database rather than utilizing TSQL to encrypt individual columns. Outside the SQL Server authentication and access should function as normal.

Reason: avoid customization and change to a vendor applicaiton, and satisfying the group security ghouls by being able to state definatively that the data within the database is encrypted.

The database is small as it contains only financial statement data, so performance should not be an issue.

You may gain some insight from this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1784536&SiteID=1

Encryption speed?

Has anyone started using encryption yet? Is it noticably slower than not using it? For the record Im not refering too column encryption, but "network" (not sure what else to call it) encryption when it's encrypted between SQL Server and the client.We did some encryption testing a while back because a client wanted at-rest encryption. Hardware solutions between the storage and sql server seem to have the least impact on data delivery, while software solutions eat up mega cpu cycles.

There was one product that used a card to do the hardware encryption, but also had software installed as a backup in case of h/w failure so it could at least limp along until the h/w could be replaced.

Either way, there will be some performance loss. How much depends on your system(s). The vendors were more than willing to give us test time.sql

Encryption related overflow?

Recently restored a SQL 2000 database to a SQL 2005 Server. The database contains a series of user stored procs (one calls upto 5 other sps) which are all encrypted using 'WITH ENCRYPTION' clause. When run on SQL 2000 Server this runs without error. When run on SQL 2005 Server it reports an error:

Msg 565, Level 18, State 1, Procedure SPDM_MP1_SOURCE59, Line 5143

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Investigating the error line reported does not reveal any problems with the sp and the error line number reported is not always consistent.

However, altering the stored procs so they are not encrypted and it all runs without error. Is there a compatibility issue running SPs encrypted on 2000 on a 2005 Server?

moving to engine forum, someone should be able to help.|||ok, how about sql security forum.|||

Can you try to repro this issue after starting the server with the -y565 argument, to have it produce a stack dump? Once you have the dump, please report this issue at http://connect.microsoft.com/feedback/default.aspx?SiteID=68 and attach the dump file to the report.

Thanks
Laurentiu

|||

Hello,

We encountered the same problem. It also depends on the computer. The same process with the same data can fail on a server and run correctly on a laptop.

A work-around is to make the stored procedure smaller. How can we be sure that the stored procedure is small enough that the problem is not produced at the client? What is the limit on the total lines of a stored procedure when using "With Encryption"?

Greetings

|||

Hello,

Example of a procedure:

CREATE PROCEDURE [dbo].[p_stack_overflow]

WITH ENCRYPTION

CREATE TABLE #test_tmp2 (column0 INT, column1 INT, column2 INT, column3 INT, column4 INT, column5 INT, column6 INT, column7 INT, column8 INT, column9 INT, column10 INT, column11 INT, column12 INT, column13 INT, column14 INT, column15 INT, column16 INT, column17 INT, column18 INT, column19 INT, column20 INT, column21 INT, column22 INT, column23 INT, column24 INT, column25 INT, column26 INT, column27 INT, column28 INT, column29 INT, column30 INT, column31 INT, column32 INT, column33 INT, column34 INT, column35 INT, column36 INT, column37 INT, column38 INT, column39 INT, column40 INT, column41 INT, column42 INT, column43 INT, column44 INT, column45 INT, column46 INT, column47 INT, column48 INT, column49 INT, column50 INT, column51 INT, column52 INT, column53 INT, column54 INT, column55 INT, column56 INT, column57 INT, column58 INT, column59 INT, column60 INT, column61 INT, column62 INT, column63 INT, column64 INT, column65 INT, column66 INT, column67 INT, column68 INT, column69 INT, column70 INT, column71 INT, column72 INT, column73 INT, column74 INT, column75 INT, column76 INT, column77 INT, column78 INT, column79 INT, column80 INT, column81 INT, column82 INT, column83 INT, column84 INT, column85 INT, column86 INT, column87 INT, column88 INT, column89 INT, column90 INT, column91 INT, column92 INT, column93 INT, column94 INT, column95 INT, column96 INT, column97 INT, column98 INT, column99 INT, )

INSERT INTO #test_tmp2

VALUES (100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100)

This insert statement is written 3000 times in the procedure.

When executing the statement, SQL Server 2005 throws this error:

Msg 565, Level 18, State 1, Procedure p_stack_overflow, Line 973

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Without encryption there is no problem. In SQL Server 2000 it works fine, even with encryption.

Using a while loop also solves the problem, but the case was produced as an example.

Thanks

Wouter

|||

Hello,

This is a known bug in SQL2005. It only affects encrypted modules that are very large in size; large enough to be the order of the stack size. Until the fix is released, you can workaround this issue by either not using encryption (if that is an option), or breaking up the module into smaller pieces.

Thanks

|||

Thanks for the answer.

The problem still remains that we don't know how many lines a procedure can contain on a specific configuration. Does this problem depend on hardware or software configuration?

Our product will be installed at the client in the near future. Can we presume this fix will be released in SP2?

Thanks

|||

This fix did not make it into SP2. You can consider contacting Customer Support Services and request a hotfix if this is blocking you.

Thanks

|||

Hi all,

Just for information, there was a fix available for this issue for 1 week.

Encryption related overflow?

Msg 565, Level 18, State 1, Procedure SPDM_MP1_SOURCE59, Line 5143

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Investigating the error line reported does not reveal any problems with the sp and the error line number reported is not always consistent.

However, altering the stored procs so they are not encrypted and it all runs without error. Is there a compatibility issue running SPs encrypted on 2000 on a 2005 Server?

moving to engine forum, someone should be able to help.|||ok, how about sql security forum.|||

Thanks
Laurentiu

|||

Hello,

We encountered the same problem. It also depends on the computer. The same process with the same data can fail on a server and run correctly on a laptop.

Greetings

|||

Hello,

Example of a procedure:

CREATE PROCEDURE [dbo].[p_stack_overflow]

WITH ENCRYPTION

INSERT INTO #test_tmp2

This insert statement is written 3000 times in the procedure.

When executing the statement, SQL Server 2005 throws this error:

Msg 565, Level 18, State 1, Procedure p_stack_overflow, Line 973

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Without encryption there is no problem. In SQL Server 2000 it works fine, even with encryption.

Using a while loop also solves the problem, but the case was produced as an example.

Thanks

Wouter

|||

Hello,

Thanks

|||

Thanks for the answer.

The problem still remains that we don't know how many lines a procedure can contain on a specific configuration. Does this problem depend on hardware or software configuration?

Our product will be installed at the client in the near future. Can we presume this fix will be released in SP2?

Thanks

|||

This fix did not make it into SP2. You can consider contacting Customer Support Services and request a hotfix if this is blocking you.

Thanks

|||

Hi all,

Just for information, there was a fix available for this issue for 1 week.

Encryption related overflow?

Msg 565, Level 18, State 1, Procedure SPDM_MP1_SOURCE59, Line 5143

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Investigating the error line reported does not reveal any problems with the sp and the error line number reported is not always consistent.

However, altering the stored procs so they are not encrypted and it all runs without error. Is there a compatibility issue running SPs encrypted on 2000 on a 2005 Server?

moving to engine forum, someone should be able to help.|||ok, how about sql security forum.|||

Thanks
Laurentiu

|||

Hello,

We encountered the same problem. It also depends on the computer. The same process with the same data can fail on a server and run correctly on a laptop.

Greetings

|||

Hello,

Example of a procedure:

CREATE PROCEDURE [dbo].[p_stack_overflow]

WITH ENCRYPTION

INSERT INTO #test_tmp2

This insert statement is written 3000 times in the procedure.

When executing the statement, SQL Server 2005 throws this error:

Msg 565, Level 18, State 1, Procedure p_stack_overflow, Line 973

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Without encryption there is no problem. In SQL Server 2000 it works fine, even with encryption.

Using a while loop also solves the problem, but the case was produced as an example.

Thanks

Wouter

|||

Hello,

Thanks

|||

Thanks for the answer.

The problem still remains that we don't know how many lines a procedure can contain on a specific configuration. Does this problem depend on hardware or software configuration?

Our product will be installed at the client in the near future. Can we presume this fix will be released in SP2?

Thanks

|||

This fix did not make it into SP2. You can consider contacting Customer Support Services and request a hotfix if this is blocking you.

Thanks

|||

Hi all,

Just for information, there was a fix available for this issue for 1 week.

Encryption related overflow?

Msg 565, Level 18, State 1, Procedure SPDM_MP1_SOURCE59, Line 5143

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Investigating the error line reported does not reveal any problems with the sp and the error line number reported is not always consistent.

However, altering the stored procs so they are not encrypted and it all runs without error. Is there a compatibility issue running SPs encrypted on 2000 on a 2005 Server?

moving to engine forum, someone should be able to help.|||ok, how about sql security forum.|||

Thanks
Laurentiu

|||

Hello,

We encountered the same problem. It also depends on the computer. The same process with the same data can fail on a server and run correctly on a laptop.

Greetings

|||

Hello,

Example of a procedure:

CREATE PROCEDURE [dbo].[p_stack_overflow]

WITH ENCRYPTION

INSERT INTO #test_tmp2

This insert statement is written 3000 times in the procedure.

When executing the statement, SQL Server 2005 throws this error:

Msg 565, Level 18, State 1, Procedure p_stack_overflow, Line 973

A stack overflow occurred in the server while compiling the query. Please simplify the query.

Without encryption there is no problem. In SQL Server 2000 it works fine, even with encryption.

Using a while loop also solves the problem, but the case was produced as an example.

Thanks

Wouter

|||

Hello,

Thanks

|||

Thanks for the answer.

The problem still remains that we don't know how many lines a procedure can contain on a specific configuration. Does this problem depend on hardware or software configuration?

Our product will be installed at the client in the near future. Can we presume this fix will be released in SP2?

Thanks

|||

This fix did not make it into SP2. You can consider contacting Customer Support Services and request a hotfix if this is blocking you.

Thanks

|||

Hi all,

Just for information, there was a fix available for this issue for 1 week.

Thursday, March 29, 2012

encryption--the sequel

encryption--the sequel

encryption--the sequel

Tuesday, March 27, 2012

Encryption; SQL Server 2005 & Windows 2003 Server

Encryption; SQL Server 2005 & Windows 2003 Server

Encryption with SSAS 2005

encryption with certificate

Encryption with Analysis Services 2005

Encryption Varbinary Length

Encryption using MS SQL 2005

Encryption using MS SQL 2005

Encryption Standards

Encryption Standards

Encryption Standards

Encryption SQL 2005

Encryption speed?

Encryption related overflow?

Encryption related overflow?

Encryption related overflow?

Encryption related overflow?

Empty Column

Blog Archive

About Me