I support an accounting application that uses SQL as the db engine. One of our clients is asking about encrypting of certain data within the database. The developer does not encrypt the data nor have they any plans to. My question is, can we turn on encryption of a field in one of the data tables that will not require decryption by the accounting application. In other words, if we set encryption at the db level, can the server itself decrypt the data when it's called by the application? Or does the application have to do the decryption?
Hi, I have a SSN field that I need to encrypt. Only persons who wants to get information about a user should be able to decrypt that field. What is the best way to encrypt and decrypt the ssn field? Thanks
hi i am doing the project in sql sserver 2005 so i want help from u the following topics i want "HOW TO ENCRYPT THE DATA IN COLUMN OR ROW IN sql server 2005" PL REPLY SOON MY MAIL ID IS
sarunprasadmtechis@gmail.com if u get any idea pl send mail to the above address pl..
Right-clicking "Protocols for MSSQLSERVER" under the Sql 2005 Server Configuration Manager I find the "Force Encryption" option. Right-clicking the SQL Native Client Configuration, Client Protocols, I find "Force Protocol Encryption"
I believe these can be used together to force the connection between a client (running the SQLNC client) and a sql 2005 server to be encrypted. In other words, the data "in motion" can be encrypted.
1. Is my understanding correct?
2. Do I need to use a certificate to make this work?
I have an application that has stores sensitive data in an SQL server and I am currently handling this through my ASP.NET application using the encryption classes in C#.
One of the things we would eventually like to be able to do is use other programs (like Microsoft Access) to run advanced Querys on the tables and retrive the data. With the encyption being done in C#/ASP all that would be returned would be the encrypted data.
I wondering if there is a way to build a layer to encrypt/decrypt data at the database level, my searches haven't yeiled to much info (As well as a trip to Borders) as I haven't seen any books that even touch on this.
I don't know a lot about SQL right now (Mainly only MySQL so Stored Procs and all that stuff are really new to me) so I don't know if I would go about it this way or not? (Using a stored procedure)
Can anyone reccommened where I should start to learn about accomplishing this? Books are usually the best help but I'll take any kind of information that can be thrown my way :-)
Hello,I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I havebeen working with SQL Server, Building ASp WebSites for many years now.I am by no means an expert - nor have I had ANY formal training. So ebarwith me if my questions seem elementary...I have some questions regarding sensitive data and encryption.There is a project that is headed my way were the social security numberis being used as the unique identifier for an account. I have alwaysused as identity column as a unique identifier. What would be the prosand cons of using the SSN as a unique identifier?#1 How do I go about encrypting the number to store in the DB. Is thisdone within SQL Server? Or before the data is inserted?#2 Is it possible to use an encrypted field as a unique identifier?My gut tells me to use the identity column , encrypt the SSN and not useit as any part of an identifier.Thank You for your Help.. Happy New Year!Please Reply to the Newsgroup.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have creating a stored procedure in sql server 2005 to encrypt a parameter value that comes from an asp.net page. this value is of type varchar.
Now i have declared a paramater as varbinary to accept this value in the stored procedure. And encrypting it using symmetric key which is encrypted by certificate.
Now when i enter a value into the textbox in asp.net page. it shows
"implicit conversion from varchar to varbinary not possible" pls tell me what is the solution? do i have to convert the value in vb code while accessing the value of the textbox or do i have to convert the value while passing the value when call stored procedure.
pls tell me the solution so that i don't jave to change any coding in vb.
CREATE TABLE TabEncr ( id int identity (1,1), NonEncrField varchar(30), EncrField varchar(30) )
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword' CREATE CERTIFICATE my_cert with subject = 'Some Certificate' CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert
OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert INSERT INTO TabEncr (NonEncrField,EncrField) VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value')) CLOSE SYMMETRIC KEY my_key
OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField)) FROM dbo.TabEncr CLOSE SYMMETRIC KEY my_key
What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005
I want to encrypt some audio files before inserting them in a SQL server database. I want to use the built in sql server encryption. I found that the encrypt by key function accepts only text (nchar,nvarchar,etc). I was thinking or reading the audio file's bytes and change them to a string and then insert them in the database. I just wanted to take opinions here. Is there a better way to do this? thanks in advance
I need to find a way of encrypting and decrypting data from SQL server 2000. I need to do this as transparently as possible, which is why I need to do this if at all possible before my web application encounters the data.I know this is possible without 3rd party applications using SQL server 2005, as I have a working implementation already; however I need to do this with SQL server 2000 as upgrading is not an option. Using a 3rd party product to encrypt/decrypt is also not possible.Any help much appreciated.Matt Rose
We are looking at setting up peer-to-peer transactional replication between two databases. We have a customer requirement to encrypt the SSN in this database. I have configured replication successfully. I have also successfully encrypted the SSN using a symmetric key (with encryption by certificate). What I haven't done yet is set up encryption to work across a replication topology.
What steps would I have to follow in order to be able to encrypt the SSN on one server, replicate it to the subscriber, and then decrypt the SSN on the subscriber? For this scenario, is there a better way to handle encryption other than a symmetric key encrypted by a certificate?
I have a SSIS project where I am transferring data from DB2 table to SQL Server table. There is a column called REC_ID which I need to encrypt before we store it in SQL Server. Now, SQL Server has buildin encryption functionality and we need to use that as there are views that will decrypt this column and give data to authenticated users.
So, the question is, is there anyway that I can encrypt the column data in my SSIS package using my target SQL server database key and using SQL server encryptbykey function while transferring?
I have never used any type of encryption for SqL Server 2000, but I have recently been asked to research what would be involved to encrypt data like social security numbers.
Since I have no idea where to begin, I was wondering if someone could point in the right direction? Do I need to buy a third party tool to perform this, are security certificates involved? Overall, I need information on everything that I need to encrypt data using SQL Server 2000, and How to perform the tasks!!!
I've been using SSIS for a while and think I get the gist of how it works (and it works nicely!).
However, because of the configuration on my machine I am constantly moving the dtsx files around (long story). Sometimes I make a full image backup and then later restore those SSIS packages.
The problem is that even if I copy the dtsx files to a different directory and then copy them back (without making any changes) I get a 'Cannot Aquire Connection' message. Other times I get errors that have to do with the encryption of the individual package. For those, I just reset the package to save with a password, save it, and then reopen it and can resave it again with the userkey encryption. This one occured when I move the packages from one machine to another.
Is there a best (or at least good) way to save packages with the sensitive data that won't force me to reconfigure it each time I move a package around? Or have to enter passwords each time?
Also, what is the advantage of saving the files in SQL Server? I assume you are not actually saving the files, just the data about them. I just don't understand it enough to even ask an intelligent question about it.
I have the following requirement. I need to store the password for the connection manager in the configuration file. The sink for the configuration file is SQL Server. Though the password field appears as "******" the actual value is being taken as ""******" itself. If i update the SQL server table with the correct value, then the package starts working. But, the password is shown as clear text.
If i write logic to encrypt the password column in the configuration table, is there a way to tell the SSIS execute engine to decrypt the password before using the same for making the connection.
Is there a place holder, where i can write the decrypt code so that the decrypted password can be sent to the execution engine?
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
Hi, I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
I have a sql 2005 DB that stores personal information including Social Security #. How can i encrypt the SSN in Sql and then Decrypt it in a asp.net 2.0 page?Thanks
Hello: I have an old database created in SQL Server 2000, now I can open, access it in SQL Server 2005 Management Studio, but I can't create Data Diagrams. Please advise on how to make it work.... Thank you
My web project (ASP.NET 2.0 / C#) runs against sql server 2000 and uses the System.Data.SqlClient.using System.Data.SqlClient; I use System.Data.SqlClient.SqlConnection and System.Data.SqlClient.SqlCommand to make the connections to the database and do selects and updates. Is it correct to continue to use these against SQL Server 2005? I ask because I made a connection string (outside of .Net) for SqlServer 2005 using the SQL native provider and it had the following - Provider=SQLNCLI.1 and any connection strings I had made (also outside of ASP.NET) fro SQL Server all used Provider=SQLOLEDB.1. This is why I wondered if there is a different SqlClient in .Net 2.0 for SQL Server 2005? Cheers Al
I am using Windows 2003 Server English Version. I wanna store the big-5data so I install the sql server 2000 as if i install it in the Windows2000 with Server Collation of the Chinese_Taiwan_Stroke_CL_AS.However, the data are stored into the database server in unicodeinstead of big-5 in that of windows 2000 OS.I would like to ask how i can set so that the Sql Server 2000 can storethe big-5 data
I am manually replicating parts of a SQL Server CE database (running windows mobile 5.0) to a centralized SQL Server 2000 database.
My program is throwing an exception whenever I try to insert an image data type into the 2000 server from the PDA. I am using parameterized queries.
Error is as follows: [error] System.Data.SqlClient.SqlConnection.OnError() at System.Data.SqlClient.SqlInternalConnection.OnError() at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run() at System.Data.SqlClient.ExecuteReader() at System.Data.SqlClient.ExecuteNonQuery() at PDASync.Database.ExecuteIDRemote() [/error]
The code for my ExecuteIDRemote method works fine for other queries. It also works if I remove the image column from the offending query.
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
Can anyone help me regarding encrypting a password.
While i put my password in a table, it shd be encrypted form so that none of the user shd able view that and for admin purpose i shd able to view, that is, i shd able to decrypt the password show to the user. The password is stored in a table as a field.
I was wondering if any of you had any idea about how I could achieve to encrypt a certain field(s) or a whole table in one of the table in my Database. Somewhat, I want to hide the password field from everyone's curious eye.
IF possible please also reply to this email : alexiscloutier@digisoftcorp.com
Hi,We want to encrypt MS Sql Server data files - .mdf and .ldf withlogged in user certificate and make sure that MS Sql Server service(running as Local System Account) can decrypt it.Is it possible to encrypt data files with a certificate that residesin logged in user'scert store and also MS SQL Server Service 'service account's certstore?You can access 'service account's cert store through mmc -
