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.
View 3 Replies
ADVERTISEMENT
Apr 21, 2007
I found that while using encryption and decryption by keys and certificates thsere is no security at all.
if we uses master key the sysadmin can decrypt
but if we use private key (encryption by password), how do we pass the password so that profiller didn't show it?
View 1 Replies
View Related
Oct 30, 2007
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.
View 3 Replies
View Related
Dec 12, 2006
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)
DECLARE@Index SMALLINT,
@i SMALLINT,
@j SMALLINT,
@t TINYINT,
@k SMALLINT,
@CipherBy TINYINT,
@Cipher VARCHAR(8000)
SELECT@Index = 1,
@i = 0,
@j = 0,
@Cipher = ''
WHILE @Index <= DATALENGTH(@Text)
BEGIN
SELECT@i = (@i + 1) % 256
SELECT@j = (@j + b.v) % 256
FROM@Box b
WHEREb.i = @i
SELECT@t = v
FROM@Box
WHEREi = @i
UPDATEb
SETb.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
FROM@Box b
WHEREb.i = @i
UPDATE@Box
SETv = @t
WHEREi = @j
SELECT@k = v
FROM@Box
WHEREi = @i
SELECT@k = (@k + v) % 256
FROM@Box
WHEREi = @j
SELECT@k = v
FROM@Box
WHEREi = @k
SELECT@CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
@Cipher = @Cipher + CHAR(@CipherBy)
SELECT@Index = @Index +1
END
RETURN@Cipher
END
Peter Larsson
Helsingborg, Sweden
View 20 Replies
View Related
Jan 3, 2008
This is related to post :
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78552
got a issue with this one..im not sure why..
My results are as follows:
Select dbo.fnEncDecRc4('Orange12345', 'Hello123')
Output : ,M gl
Select dbo.fnEncDecRc4('Orange12345', ',Mgl')
Output : M
i am not able to decrypt it. Any idea why this is hapenning? Does it has to do something with regional settings?
View 10 Replies
View Related
Jan 24, 2008
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?
Thanks
View 5 Replies
View Related
Oct 30, 2006
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.
View 1 Replies
View Related
Sep 17, 2003
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
View 12 Replies
View Related
Feb 19, 2008
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.
View 2 Replies
View Related
Dec 28, 2007
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.
Many thanks
View 1 Replies
View Related
May 4, 2015
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,
View 0 Replies
View Related
May 4, 2006
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...
declare @thePassword as varbinary(128)
,@inTargetPassword as varchar(255)
,@pwd3 as varchar(255)
,@theUserId bigint
set @theUserId= 124564
set @inTargetPassword = 'test'
OPEN SYMMETRIC KEY Key1
DECRYPTION BY CERTIFICATE sqlSecurity;
Select @pwd3=EncryptByKey(Key_GUID('Key1')
, @inTargetPassword, 1, HashBytes('SHA1', CONVERT( varbinary, [UserObjectId])))
from table1 where UserObjectId= @theUserId
close symmetric key Key1
View 6 Replies
View Related
Jan 16, 2008
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
View 1 Replies
View Related
Apr 17, 2001
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.
Appreciate any ideas you can give!
Cheers & TIA
View 1 Replies
View Related
Nov 26, 2015
Is there any way i could apply encryption on a varchar type column without changing its datatype to varbinary.
View 6 Replies
View Related
Apr 25, 2006
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
View 6 Replies
View Related
Sep 11, 2015
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?
View 3 Replies
View Related
Nov 23, 2015
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?
View 9 Replies
View Related
Dec 24, 2007
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?
Regards
Jerminxxx
View 7 Replies
View Related
Oct 22, 2014
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?
View 1 Replies
View Related
Nov 19, 2015
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.
View 3 Replies
View Related
May 8, 2007
Hi,
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?
Thanks for your help.
View 3 Replies
View Related
Jan 29, 2007
Msg:
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.
"open symmetric keys" is not allowed in a function?
if I want to encrypt a string in a function by sql2005's internal functions ,how can I do ?
View 3 Replies
View Related
May 11, 2015
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.
Email
-------
1 | 0x00703529AF46D24BA863A3534260374E01000000328909B51BA44A49510F24DF31
C46F2E30977626D96617E2BD13D9115EB578852EEBAE326B8F3E2D422230478A29767C
2 | 0x00703529AF46D24BA863A3534260374E01000000773E06E1B53F2C57F97C54370FECBB45B
C8A154FEA5CEEB9B6BB1133305282328AAFAD65B9BDC595F0006474190F6482
3 | 0x00703529AF46D24BA863A3534260374E01000000C9EDB1C83B52E60598038D832D34
D75867AB0ABB23F9044B7EBC76832F22C432A867078D10974DC3717D6086D3031BDB
But, how do I work around this?
View 8 Replies
View Related
Jul 20, 2005
....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.
View 2 Replies
View Related
Oct 30, 2007
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?
View 2 Replies
View Related
Nov 3, 2015
How to encrypt the java application code using the 'with encryption' clause from sql server stored procedure or function.
View 3 Replies
View Related
Apr 30, 2008
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 Related
Feb 8, 2008
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?
Thanks
View 1 Replies
View Related
Nov 6, 2007
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
Thank you,
Tom Hundley
View 4 Replies
View Related
Jul 19, 2007
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.
View 1 Replies
View Related
Mar 16, 2007
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
View 10 Replies
View Related
Aug 1, 2005
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
View 3 Replies
View Related
