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 RepliesI 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 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 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
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
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?
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
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 have setup database mirroring with witness server. To prevent unnecessary failover because of network slow or other issue, I changed the timeout setting as
ALTER DATABASE <Database Name>
SET PARTNER TIMEOUT 120
Which I understand if connection is broken between principal and mirror, principal database will wait for 120 second and after that only automatic failover will happen.
If this is true, it does not happen in my case. Failover happens before120 second.
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
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?
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.
Sql server 2008R2 (SP2) Ent, PROD DB myDB was encrypted. During Release mistakenly (Vendor created script blames some settings in ...- actually does not matter) Decryption started (ALTER .. SET ENCRYPTION OFF) as we got from ErrorLog.
For some reason initial encryption scan was aborted and then mentioned command: ALTER ... OFF was issued again. What we have now (after 60 h of decryption- encryption took only 2.5 h)- is_encrypted = 0 in sys.databases, encryption_state = 5 (decryption in progress) in sys.dm_database_encryption_keys (percent_complete= 0). But it seems myDB is still encrypted- I made a backup of myDB and tried to read it (restore filelistonly) from other server (with no encryption)- failed- asked for key. Seems metadata was changed when initial scan during decryption started but then stuck and (if I am correct) decryption was never completed. Question- any similar experience? How we can fix meta- data, i.e. assuming that myDB is still encrypted we should have is_encrypted = 1 and encryption_state = 3 (encrypted).
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
I have a SP SQL server that uses Handshake for the web parts. I am getting an error on SharePoint about 'An error occurred during Service Master Key Decryption' inside the web parts of the page, everything else comes up, from what I have researched MS says go under SQL Configuration Manager and change the service account. Is this the correct course of action for this type of error? I am just having a hard time believing that changing the engines service account will stop this issue, this account is used on several SQL server with no issues.
MCSA SQL Server 2012
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
Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)
For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?
I am search for coding criteria I need create a stored procedure with execute as and along with encryption. How can I use the same ? My main motive is to create proc with execute as a user also at the same time I need to encrypt the same from other users seeing the code.
The below query is getting errors:
Create procedure testproc
with execute as 'user' and with encryption
as truncate table some table
We have a database. It is enabled for mirroring. We need to delete the old records. That is around 500k records from a table. But it has foreign key relation. How to do in Production servers these kind of deletes?
View 2 Replies View RelatedWe have a database in SQL Server 2008 R2 with mirroring and want that replication is done by dedicated network.We stop the endpoint and when we try to run the following command, syntax error occurred:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '192.168.1.14'.
What is the correct syntax of the command line below?
ALTER ENDPOINT Endpoint_Mirroring AS TCP (LISTENER_IP = '192.168.1.14')
I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Hi,
I need to encrypt certain fields of tables with minimized changes required to my application. I want the database only available to my application so I want to use password to protect the encryption key.
Currently, in SQL 2005, I created view and use INSTEAD OF trigger to encrypt/decrypt data to underlyer table. I also having code at application startup, pass my password to a stored proc to open these key so that my view can encrypt/decrypt data accordingly.
I read some posts mentioned that SQL 2008 supports transparent data encryption. May I know how does it help in my case?
Please advice.
Thank you
Hi,
Does anyone know how to encrpyt a field in a table created in SQL Server Database.
I have three win2k8 r2 servers. Â 2 are running SQL 2008 r2 and are mirrored. The 3rd server is a witness server. Every so often we get errors and failovers of the mirror. The communication errors are between the witness server and sql servers. No errors between SQL servers. There seem to be no network issues and happen randomly.Â
Event id 1474 and 1479.Â
The mirroring connection to "TCP://witnesssrv:5022" has timed out for database "DB" after 30 seconds without a response. Â Check the service and network connections.Â
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://witnesssrv:5022'.
I have setup database mirroring for one big database.But now Transaction log is full and server is down. What should i do? We do bulk-insert in that database.
View 3 Replies View RelatedUsing SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
View 5 Replies View RelatedHello, My question is simply: according to BOL, a DB snapshot taken from a Mirror is read-only. Say I wanted a temporary (disposable) read-write DB created from the snapshot. Is there a way to generate a writable temp DB (that's not tempdb) from the snapshot? Thanks,AK
View 1 Replies View RelatedI need to migrate my SQL server Mirror Database to a new server. my current setup is as below
1. server A principal (192.168.1.100)
2. server B Mirror   (192.168.1.200)
Now i have a new server (Server C) to replace server B as below
1. server A principal (192.168.1.100)
2. server C Mirror   (192.168.1.300)
My question is how to migrate mirror db to new server without any affect or downtime on principal server.
note: SQL SERVER 2008R2 EE(64BIT), Win2008R2 EE 64bit.
Dear,
Are possible build a solution with SQL Server 2005 Cluster and Database Mirroring? I have the following scenario:
1) Site A
SQL Server 2005 with 2 nodes and active/passive
2) Site B
SQL Server 2005 for Disater Recovery with Log Shipping.
Are possivel change Log Shipping by the Database Mirroring? If is possible, are better:
a) Synchronous(with or without witness)
b) Asynchronous
Thanks a lot! Sandro.
sgpcosta@hotmail.com
Hi there.
I´m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.
The tests i´m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.
My question here is: What if i use Sql Server failover mechanism?
I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?
Hi All,
I get a general question about the feacture "Database Mirroring". Is it support by SQL Server Standard. Edition without SP1?? Of i still need to upgrade the server from SQL Server 2005 to SQL Server 2005 with SP1?
Another question: are there any problem if the one machine(server) has installed SQL server 2005 Standard edition and the other with "Developer Editon".
I hope some one can answer my question. Thanks anyway!
Regards,
Pat
Subject:RijndaelManaged decryption from SQL Server
Issue: I am trying to move the decryption code from C# to SQL Server 2005.
Web.config
...
<symmetricCryptoProviders>
<add algorithmType="System.Security.Cryptography.RijndaelManaged, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=xxxxxx" protectedKeyFilename="C:Keysfffff.key" protectedKeyProtectionScope="LocalMachine" type="Microsoft.Practices.EnterpriseLibrary.Security.Cryptography.SymmetricAlgorithmProvider, Microsoft.Practices.EnterpriseLibrary.Security.Cryptography, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null" name="RijndaelManaged"/>
</symmetricCryptoProviders>
...
.cs file
....
decString = Cryptographer.DecryptSymmetric("RijndaelManaged", encString);
....
I have to disable newly implemented database encryption. It's a necessity unfortunately. Can I do this during production hours without much of a hit? I know I have to restart the instance after it's done. Can I expect performance impacts or other issues?
View 1 Replies View Related