Does anyone have any specific performance information using AES or other encryption schemes supported by SQL Server 2005?
- What method did you find works best?
- Did you encrypt any XML Data?
- Did you encrypt specific columns or whole tables?
Thanks,
Michael
Does anyone have any information I can look at that may show me what resource overhead I may see by using encryption in SS2K5?
I am interested in memory consumption and other metrics of that nature when encrypting and decrypting data.
Especially when you have several hundred to several thousand connections.
Thanks ALL!
Hello All,
Here is the SQL 2005 encryption environment:
1. Clustered SQL 2005 (enterprise edition) on windows 2003. HP (quad processor) with CPU affinity set to all processors.
2. Table structure where encrypted data will be stored has two varbinary (max) columns to store encrypted data. The columns are varbinary (max) b/c the data size could be more that 8K.
3. Encryption using AES (tried both 128/256) algorithm with symmetric keys.
When inserting data in the columns, CPU is staying at 50% when inserting records. Any ideas why this would be happening. Any suggestions on improving performance is appreciated..
Thanks..
I've been asked to put together an estimation for the performance impact that replication would have on our database server during a particular operation. I know that this depends on a lot of different factors, including:
* Number of articles being replicated
* Types of articles being replicated
* Number of DML transactions that would result in delivery of replicated data
Any way to turn this into a meaningful metric?
I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.
best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.
SELECT ID FROM table WITH (NOLOCK)
                    WHERE dbo.DecodeFunction(column) = 'value'
When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.
Hi
I am trying to encrypt data using a symmetric key which is encrypted by certificate. I do not want grant control on these objects to the users who wants to decrypt this data. Instead I have created a udf with execute context as "dbo" and used DecryptByKeyAutoCert built-in function.
Now this works fine but large data operations this is extremely slow. It takes around 10 minutes to select decrypted data whic in comparision takes 11 seconds when DecryptByKey function is used.
But I am not sure when DecryptByKey is used, whether the symmetric key is decrypted by the private key of the certificate or not. Can somebody give some explanation of this ?
Also, I can not have a UDF with these following steps
1. Open symmetric key
2. Convert secretdata using DecryptByKey
3. Close Symmetric Key.
4. return decrypted value.
Can some one give some insights on this ?
I am trying to implement encryption but have run into some serious performance issues. I am required to encrypt the SSN in our database. In and of itself, this is not a problem. The problem comes in because there is also a need to be able to query the table based on the SSN. Since the SSN is encrypted, the query basically performs an index scan, decrypting each value as it goes along. As a result, the query for one record out of 10 million records in the table takes three minutes. It needs to occur immediately.
If I could encrypt my SSN parameter and then compare it to the encrypted value in the column, it would work fine. Unfortunately, everytime you encrypt a particular value, the resultant encrypted value is different. Hence, I have to decrypt the column to match my parameter, instead of encrypting th parameter to match the column.
Does anyone have any suggestions to help alleviate this problem?
Thanks,
I am in the stage of design for an application that uses SQL server 2005. We intended to encrypt some sensitve data using the encryption features in SQL server 2005. we will use symmetric key encryption. The question here is which symmetric encryption algorithm has the best performance? how much does the key size affect the perfromance? the data to be encrypted will be some lines of text equal to a word document.
any ideas?
Hi,
I have a central database server that is runnning on SQL 2005 standard edition and Windows server 2003 standard as OS.
I realise that I can use SQL statements to encrypt and decrypt the data inside the standard SQL.
However, how do I read and write the data via an web application coded in C#.net and is also running on the same machine?
Another issue is, I need to replicate some of the data in this SQL standard over to a SQL mobile running on a mobile device running on Windows CE 5.0.
The mobile device also needs to read and write data to the encrypted data via a C#.net application.
Question is, with all these requirements to be met, can I use AES? I know that AES is not available on Windows XP and Windows Server 2000 and I cant find AES in the .net compact framework.
how do i go about ensuring security? how do I ensure that the symmetric key is the same both on the SQL standard and SQL mobile?
thank you.
hi,Is it over head to use SqlTransaction(begin, commit, rollback) for a single transaction.am not using application block or enterprise library.only a single insert statement.
View 3 Replies View RelatedAs part of dealing with a locking problem I am fine tuning a stored procedure
that updates a table. The application updates a row by changing every single column except for the primary key, whether 1 or all of the columns have
been modified. Although easier to code, this strikes me as taking a sledge hammer to a nut.
Could anyone tell me if there is any benefit in attempting to break this down. That is, code the stored procedure so that only those columns being changed are modified. I am thinking this might reduce dramatically the overhead of writing to the transaction log and making the changes to the actual row.If the benefit is non existant (or insignificant) because of the way Sql Server updates a row it will obviously be a waste of time to generate
dynamic sql.
Thanks
Hey I need to know the overhead associated with SQL Server tables?
And what the overhead is comprised of ,would also be helpful.
Please
Does anyone have any benchmarks for the amount of overhead caused by autoshrink of the log and having autostats enabled? We have a customer that insists that turning off these options was necessary to eliminate a performance problem they were having (Query timeouts), but we are not convinced that these two options would have generated enough overhead to have been the root cause (they also rebuilt all their indexes and made some other unspecified changes that more likely solved the problem).
We are hestitant to have them continue with these options disabled because then we need to rely on them to keep the log file shrunk and the statistics updated and because of the data changes during the day, would prefer to have stats updated automatically rather than on a fixed schedule that may not be as appropriate.
Anyway, if anyone has any feedback on overhead generated and potential performance implications of having either of these options enabled, it would be greatly appreciated.
Thanks,
Ray Rankins
Hope somebody can give me some advice .
We are having some issues with temporary tables (with # prefixes) within Stored Procedures.
When running a profile trace on them, the stored proc quite happily creates the temp # table (in fact several of them) but whenever it hits the first statement inserting data into one of them (and it doesn't matter which one), there is a 5-6 second delay.
By commenting out one and moving to the next piece of code, the same thing happens.
Following which, the rest of the Stored Prco runs fine and subsequent inserts into the # temp tables also run efficiently.
Is the stored proc getting recompiled perhaps ?
Any advice woul;d be appreciated.
we are running SQL Server 7.0, dont know whether that helps ?
Hi
I have been doing some testing with SQL Server 2000 using a packet sniffer, and have found that it is sending bytes with a value of 0x00 between each "valid" character. For example, if it was going to send "hello" over the network, it would be transmitted as "h.e.l.l.o".
Can anybody suggest why this might be. It happens regardless of what client is used - ADO.NET, osql.exe, etc. Is it something to do with the encoding used?
Everything does work fine and the data is received intact, but if these seemingly redundant bytes could be removed then it would increase performance by 100%.
Thanks
Does SQL Server 2005 not have a built in encryption function. I'm trying to INSERT and store passwords as an encrypted value in my table. Any help appreciated. Thanks.
View 1 Replies View RelatedI have a VB 6 app with a SQL 2000 database backend.
To meet company standards I need to add encryption from the VB6 app to the database. I also need to add better password protection at the database. Upgrading to SQL 2005 will help with the password protection changes I need to make and I have been told that 2005 does have some sort of Encryption built in?
Does anybody have any references or information about encrypting data in transent between a VB6 app and SQL server 2005?
Thanks
Hi everyone. I'm relatively new to the world of encryption and have a specific scenario on which I need guidance.
Scenario / Requirments:
1) Our DBA group is loading a table with SSN from Oracle into SQL 2005. They will be encrypting the SSN using the built in encryption functionality of SQL. Specifically, they are using a SQL generated Certificate. (create cert dboCert ... encryptBycert ...)
This is their preferred method of encrypting the data but they are willing to change it if I need them to. Our only requirement is that it is at least 128 bit- 256 is preferred.
2) I am returning information back to a C# class. I don't want to use the DecryptByCert function in SQL and then send the clear text across the wire between SQL and the Web server, so I need to return the data as cipher text and then decrypt it on the web server in C#.
3) I will be logging queries into another table for auditing, so I will need to re-encrypt the SSN into this new table.
It is not required, but would be ideal if I can use the same algorithm to encrypt this new table as SQL uses in the encryptByCert. This way the DBA team can decrypt both tables without using my C# code should the need arise.
How do I do this? I've figured out how to use AES in the Security.Cryptography namespace, but I've read that although symmetric encryption is much faster, it is not ideal to use in a distributed system due to key management. I€™m also not clear how to use this in SQL (not sure it matters if it€™s not the best way to go).
I'm about to start researching the Security. Cryptography namespace for asymmetic encryption using certificates, but I'm not sure how that works with the SQL Certs (are the RSA?, etc).
At this point, I'm on information overload and my head is spinning. J
Thank you,
Tom Hundley
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.
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
We are going to apply CDC on one database.
CDC is creating additional tables under System tables.
What is the performance overhead on the database by creating these tables?
I am going to access the CDC records through one ETL tool. Once read the data I am going to delete the records.
If frequency of changes are more once reading the data there may be few records will be added to the CDC. Is CDC is going to truncate the tables or only read records?
I would like to capture about 20 rows from the sysperfinfo table every30 secs on a production server. I am thinking of ways I can reduce thedisk (not network) I/O overhead of this process. Instead of readingthe table from a local SQL Agent job and writing to a local table, I amwondering if I should create the job to capture the data from a remote(less critical) server. The servers are connecting via a gigabit lineand are in the same server room on the same switch. This way theproduction server would be required to do the reads but the otherserver could take care of the I/O of the writes to the capture table.Also, this job would run from 7am to 7pm non-stop. A waitfor delay of30 seconds would control the twice a min scheduling. Running this onthe remote server would free up a few SQL Agent CPU cycles as well.This would be one less job for the production server to worry about.Thanks
View 1 Replies View RelatedI am a developer, and I have a disagreement with my DBA. He has convinced management, that SQL 2005 FullText Index is so much overhead on production, that it should NEVER be used under any circumstances. We have a Cold Fusion site, and somehow he convinced management that a bunch of Cold Fusion developers can create a more efficient full text indexing method than by using SQL 2005 Full Text Index. So now we have to come up with a method for doing this in Cold Fusion.
Is there any statistical data that could possible support or refute his statements?
Thanks
I am having a problem with some code I have in a DLL that is running in SQL Server 2005. I am trying to get some RSA encryption and decryption. The encrypt code runs in SAFE mode without a problem. The decrypt code gets and error:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "March_CryptoDecrypt":
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.KeyContainerPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Security.Cryptography.RSACryptoServiceProvider.ImportParameters(RSAParameters parameters)
at System.Security.Cryptography.RSA.FromXmlString(String xmlString)
at Crypto.DoCrypto.Decrypt(String P_text, String P_privateKey)
at SQLServerCrypto.Decrypt(SqlString P_text, SqlString P_privateKey)
Here is the decrypt code:
static public string Decrypt(string P_text, string P_privateKey)
{
string retStr;
string encryptedBlock = "";
string localTextStr = P_text;
int numberOfBlocks;
RSACryptoServiceProvider rsaProvider = new RSACryptoServiceProvider();
rsaProvider.FromXmlString(P_privateKey);
Queue<string> encryptedBlocks = new Queue<string>();
while (localTextStr.Length != 0)
{
if (rsaProvider.KeySize == 1024)
{
encryptedBlock = localTextStr.Substring(0, localTextStr.IndexOf("=") + 1);
encryptedBlocks.Enqueue(encryptedBlock);
localTextStr = localTextStr.Remove(0, encryptedBlock.Length);
}
else
{
encryptedBlock = localTextStr.Substring(0, localTextStr.IndexOf("==") + 2);
encryptedBlocks.Enqueue(encryptedBlock);
localTextStr = localTextStr.Remove(0, encryptedBlock.Length);
}
}
encryptedBlocks.TrimExcess();
numberOfBlocks = encryptedBlocks.Count;
retStr = "";
for (int cnt = 1; cnt <= numberOfBlocks; cnt++)
{
encryptedBlock = encryptedBlocks.Dequeue();
retStr +=
ASCIIEncoding.ASCII.GetString(rsaProvider.Decrypt(
Convert.FromBase64String(encryptedBlock), false));
}
return (retStr);
}
Here is the encrypt code that works:
static public string Encrypt(string P_text, string P_publicKey)
{
string retStr;
RSACryptoServiceProvider rsaProvider = new RSACryptoServiceProvider();
rsaProvider.FromXmlString(P_publicKey);
int numberOfBlocks = (P_text.Length / 32) + 1;
char[] charArray = P_text.ToCharArray();
byte[][] byteBlockArray = new byte[numberOfBlocks][];
int incrementer = 0;
for (int cnt = 1; cnt <= numberOfBlocks; cnt++)
{
if (cnt == numberOfBlocks)
{
byteBlockArray[cnt - 1] =
ASCIIEncoding.ASCII.GetBytes(charArray, incrementer, charArray.Length - incrementer);
}
else
{
byteBlockArray[cnt - 1] =
ASCIIEncoding.ASCII.GetBytes(charArray, incrementer, 32);
incrementer += 32;
}
}
retStr = "";
for (int cnt = 0; cnt < byteBlockArray.Length; cnt++)
{
retStr += System.Convert.ToBase64String(
rsaProvider.Encrypt(byteBlockArray[cnt], false));
}
return (retStr);
}
I do not see why the encrypt can run is safe mode and the decrypt can not. Does anyone have any ideas?
Thank You,
David Demland
Hello,I have been researching the use of symmetic and asymmetic encryption inSQL 2005 and I am pretty excited to give it a try. Through examples, Ican encrypt the data, but I cant figure out what to do next...What I want:1. our social security field to be encrypted so that only the person(s)that need it can decrypt it.2. prevent DBA's from decrypting the data themselfs3. Simple way to encrypt the data on the table (maybe a trigger?)I thought I would use asymmetric keys, this way I can embed the publickey into my data warehouse process to encrypt the data.I thought I would prompt the user for the private key when the reportruns, that way I wont store the key on the server.This would be a place to start.Someone in the office said that we can store the keys in Activedirectory, so maybe I could make this seemless to the user running thereport?I've found a lot of great articles that got me started, but I amneeding the next stepAny Ideas would be apprecitated!TIARoblinks to articles I have found handy:http://www.databasejournal.com/feat...int.php/3483931http://www.devx.com/dbzone/Article/29232/0/page/3http://www.sqlservercentral.com/col...rintversion.asp
View 4 Replies View RelatedHi All,Does any body know how to use encryption in sql server 2005.Is itpossible to encrypt a particular column in a table.thanks
View 3 Replies View Related
If I understand all the posts/documentation correctly am I correct in saying that sql server will not send a symmetric key outside of database.
For Eg can I use ADO.Net to get the key from database into a C# application to do encryption/decryption in the C# application outside of database. I want the C# application to be able to encrypt/decrypt data using .Net cryptography api's but use sql server as key store in addition to encryption/decryption.
thanks for the help
I can encrypt columns in sql 2005 but where do I store the key to decrypt the columns?
I can store the key in the database (or server on which the database resides) but I think that offers little security. I could store the key on another server that the sql server accesses only upon startup (though I don't know exactly how to do that). Or I could store the key on a removable drive that is read (and only needed) when the sql server starts up.
What are your ideas on this matter?
TIA,
barkingdog
Are there any pitfalls i should look out for when using the encryption in SQL 2005?
View 39 Replies View RelatedAre there any known issues with EncryptByKey/DecryptByKey on x64 machines?
I have a test script where I create a sample table and encrypt a column and later decrypt it. It works fine on my x86 box. When I run the *exact* same test script on an x64 server I'm getting unprintable characters back on the DecryptByKey. I cannont find anything I'm doing different between the two.
Has anyone seen anything like this before?
I have SQL 2005 (v9.0.3042) on Windows Server 2003. The sql server is running under LocalSystem account.
I am trying to enable SSL encryption as described in the article http://support.microsoft.com/kb/316898.
I have logged onto the machine as an administrator when creating a new certificate request in MMC. I have set "Force Encryption" to true on server and restarted the server.
However all my clients (.net code, SQL Server Management Studio) successfully connect to the server without "Encrypt=Yes". I expected to see a ssl error or some kind of error denying connection because the cliend did not request ssl ecnryption.
what am I missing? any help would be greatly appreciated.
thanks
Hello,
I store data in an .mdf file (I use SQL server 2005), because this way it's easier to move the application from one machine to another.
I've faced a problem with the encryption of the database.
Is there a possibility/way to encrypt a database file so that, if someone else finds/copies the mdf, he/she won't be able to read it.
I thought about encrypting the string values of the tables itself and decode them inside the application and encide when Inserting, but why inventing somehing that might already exist.
Thank you.
Hi There
We have been playing around with encryption in 2005. I cannot find a BOL topic that discusses dropping encryption objects such as keys.
We do the followign steps:
Create master key with password, then we create a certificate using the master key, we then create a symmetric key using this certificate and encrypt data columns.
But what i find worrying is that you can then drop the symmetric key , there are no warnings that you have objects dependant on this key for decryption.
Once you have dropped the key you cannot decrypt the data anymore?
Also the key defults the expiration date to 1 year.
WHat happens after 1 year when you have encrypted data and an expired key, or someone drops the key ? How can you ever decrypt the data after that ?
You can backup master keys nd certificates but not symmetric keys?
It seems to be that youc an very easily orphan encrypted data by the loss of the symmetric key for whatever reason, is this correct ?
Thanx