SQL Security :: Encryption 2005 - User Defined Function For Encryption And Decryption
Oct 7, 2015
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.
I created stored function with encryption. after i created i dont able to view the source code from system tables or any tool. i have get back the original source code
note: i want to stored function not for stored procedure.
This function is used to initialize the seed for the RC4 algorithmCREATE FUNCTION dbo.fnInitRc4 ( @Pwd VARCHAR(256) ) RETURNS @Box TABLE (i TINYINT, v TINYINT) AS
BEGIN DECLARE@Key TABLE (i TINYINT, v TINYINT)
DECLARE@Index SMALLINT, @PwdLen TINYINT
SELECT@Index = 0, @PwdLen = LEN(@Pwd)
WHILE @Index <= 255 BEGIN INSERT@Key ( i, v ) VALUES( @Index, ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1)) )
INSERT@Box ( i, v ) VALUES( @Index, @Index )
SELECT@Index = @Index + 1 END
DECLARE@t TINYINT, @b SMALLINT
SELECT@Index = 0, @b = 0
WHILE @Index <= 255 BEGIN SELECT@b = (@b + b.v + k.v) % 256 FROM@Box AS b INNER JOIN@Key AS k ON k.i = b.i WHEREb.i = @Index
SELECT@t = v FROM@Box WHEREi = @Index
UPDATEb1 SETb1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b) FROM@Box b1 WHEREb1.i = @Index
UPDATE@Box SETv = @t WHEREi = @b
SELECT@Index = @Index + 1 END
RETURN ENDANd this function does the encrypt/decrypt partCREATE FUNCTION dbo.fnEncDecRc4 ( @Pwd VARCHAR(256), @Text VARCHAR(8000) ) RETURNSVARCHAR(8000) AS
BEGIN DECLARE@Box TABLE (i TINYINT, v TINYINT)
INSERT@Box ( i, v ) SELECTi, v FROMdbo.fnInitRc4(@Pwd)
Hi, Does any body have a stored procedure or a function I can use? What I need is to encrypt and decrypt a password using Tiny Encryption Algorithm, SO I have an encryption scalar valued function or sproc and similarly decryption function or sproc.Now I need rolling keys to encrypt and decrypt, so I have a table which has keys used for encryption and decryption and depending on the dtae the keys are different.So I alos need a sproc to retrieve the keys.If anybody has done it before or can point me to where can I go let me know?
Database Security, we are going to use AES 256 Symmetric Encryption. We will be using RSA for Asymmetric Key Encryption, 1024 Bits.
We got the code working for the seond case but for the first, WHEN:
CREATE SYMMETRIC KEY sym_Key WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY asym_Key GO
THEN:
-- Msg 15314, Level 16, State 1, Line 1 -- Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows.
What can be the way out to be able to create the AES 256 Symmetric key.
I am planning to use XP_CRYPT for encrypting and decrypting cc#'s, passwords etc., at database level. Any suggestions or experiences on this. More info about this product at http://www.activecrypt.com/faq.htm
Hi. I have a SQL Server 2000 database that contains information I would like to encrypt. The information is a field inside a table, and I would like to encrypt this information using a key, and decrypt it in my asp.net application using that key and use the decrypted data. Please tell me how this can be done, or direct me to an article or a link on the subject. Thanks in advance.
I would like to be able to store user network passwords in a database table and be able to encrypt and decrypt using stored procs. Could anyone give me a pointer on this.
I have created mirroring... one of the column is encrypted on mirror database and I can see the decrypted result when I do query when I actually logged into server (through remote connection) but when I use the same query through using SSMS from my laptop the query result come as the column is not decrypted,
Hey I had a table with a column of data encrypted in a format. I was able to decrypt it and then encrypt it using Symmetric keys and then updating the table column with the data. Now, there is a user sp which needs to encrypt the password for the new user and put it in the table. I'm not being able to make it work. I have this so far. Something somewhere is wrong. I dont know where. Please help Thanks. I used the same script to do the encryption initially but that was for the whole column. I need to see the encrypted version of the @inTargetPassword variable. But it's not working. It doesn't give me an error but gives me wrong data...
I want to perform column level and database level encryption/decryption.... Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms... I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database... Can anybody help me out... If you have Store procedure in SQL for the same then also it ll do... Thanks in advance
Hi There is a very sensitive table that holds a very sensitive field (i.e. a person's salary). I have a requirement to programmatically encrypt it when the value is stored (I can do that quite easily in the VB client app), but there are times when a suitably 'sa' user should be allowed to perform a SELECT of the table and to view the salary field in the clear.
I think a SQL2000 user-defined function can do the job. The catch is that the client is running SQL 7 and he will only upgrade 'over his dead body', or words to that effect.
I understand that Dialog Security + certificates can be used to encryption individual dialogs. I have several demos working now that do just this.However, I don't fully understand exactly when the messages are encrypted, and if they are ever written to a queue on the initiating service prior to being encrypted. I want to make sure that admins can't simply query the transmission queues to get clear text messages, because I have strict requirements that I encrypt all personal data that is stored anywhere in a database. BOL is a little unclear on this topic. The relevant doc reads:For a dialog that uses security, Service Broker encrypts all messages sent outside a SQL Server instance. Messages that remain within a SQL Server instance are never encrypted. In dialog security, only the database that hosts the initiating service and the database that hosts the target service need to have access to the certificates used for security. That is, an instance that performs message forwarding is not required to have the capability to decrypt the messages that the instance forwards.Does this imply that message destined for an external service aren't encrypted until they leave the instance? Or does Service Broker figure out that the message is destined for a remote service and therefore applies encryption to the message_body prior to the message hitting the transmission queue on the initiating service?Many thanks, Kevin
I have a question regarding Transparent Data Encryption. I have enabled TDE on a database using the below steps:
1. Create a master key encryption by a password. 2. Create a certificate in the the user database named 'TDE_Test' protected by the master key. 3. Create database encryption key 'TDE_Test' using the certificate created in Step 2. 4. Enable encryption by using the command 'ALTER DATABASE TDE_Test SET ENCRYPTION ON'
I understand that if I need to copy this TDE encrypted database to a different SQL Instance, I have to copy the certificate from the source Instance to the destination Instance.Now my question is, do the Service Master Key and Database Master Key come into the picture here anywhere?
Are these related to TDE in any way? Do I have to take regular backups of the Service Master Key & Database Master Key as part of regular maintenance for the SQL Instance that has a TDE encrypted database?
I am using SQL server 2012 Management studio and I have some confidential data on publisher which is being replicated to subscriber and i want to revoke permission for decryption at publisher end which is only possible using Asymmetric key as it allows only private key to decry-pt the data. But problem which i am facing is,we can not take backup of asymmetric keys which i could restore at subscriber. I do not want to share the private key password with sender. Is there any way to achieve it?
Dear all I wants to run sql server user defined function when linked two server.
I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me?
I have an SSIS package built by another developer, and now that I'm running it under my login the passwords won't save. The solution and packages are setup with ProtectionLevel EncryptSensitiveWithUserKey, but how do I get the User Key to reset so I can now save passwords? I can re-enter them, but whenever I enter hte password and test it then click OK it still has the red arrow next to the connection as if there's an error. I can create new connections and those passwords save fine, but with 40-50 items in this package I hate the thought of having to go into each and change the connection.
I tried changing the package and solution to DontSaveSensitive then rebuilding and closing then reopening, I hoped there was some option to reset the User Key just as if I created the solution. If this option doesn't exist why?
We are unable to login in database due to “The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x80090331. Check certificates to make sure they are valid. Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.”we have tried to run that selfssl.exe from command prompt followed by below command and am getting the cryptographic error.
I'm trying to determine what the best way to do what basically amounts to per user encryption is.
I want to provide the ability for my users to store their passwords (to various applications, and possibly product keys, but the content is mostly irrelevant) in a retrievable format, but I want this information encrypted at rest. I'm not new to cryptography but am fairly new to encryption in sql server.
My issue arises from not seeing how I can manage (creating) the keys for the different users in a fairly automatic fashion.
The closest I've come to what I'd consider to be 'workable' is having the user 'request' access at which point they'd be asked for a password to create their master key. That master key would be used for protecting individual keys for each of the stored items. If so desired it could then continue the key chain to allow users to share specific items. Sadly I don't have the infrastructure for a CA, otherwise I'd go that route as theoretically that should 'just work' if I can assign people certs.
Ideally I'd be assigning users to an active directory security group, which would be part of an 'allowed to use the password tool' server role with appropriate permissions, and the client would authenticate (or be impersonated) through to the database with their pre-existing windows credentials, and everything else would just be magic.
Back on topic, I'm mostly looking for a way to handle this initial key creation (i.e. without a user 'requesting access' and having to create their master key w/password through an interface or support request). I expect I'll have to write an interface for the end user to manage their individual keys and "sharing" regardless of the route I take, so adding a way for them to create the initial key isn't a concern, I'm just worried I may have missed something and will be reinventing the wheel here.
Is there built in functionality I can/should look at regarding this? Or does anyone have a better way to do what I want to?
I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!' CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs' CREATE SYMMETRIC KEY ERKEY WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
....it's possible without any third party application?I need to recover some encrypted user functions but the sources have beenlost long time ago, someone can help me?--Lav.
I would like to force only certain users on certain databases to use encrypted connections for accessing our 2005 SQL Server. I've found a clunky way to do it, via registering a login event notification and using the kill statement. However, that's ugly, and I don't think there is any guarantee that the event will always fire before an evil client could get off a few evil commands. Is this supported, or is the only supported feature the forceencryption option, which is server-wide?
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.
I 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?
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. Im also not clear how to use this in SQL (not sure it matters if its 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
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.
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?
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
