SQL Server 2005 encryption with databound controls

Hi

I'm looking for ways to encrypt data in my MDF database (SQL Express) and i 
wonder if anybody had some examples on how to use it, especially in 
conjunction with databound controls using dataset's..

TIA

/Finn
-- 
Der er 10 slags mennesker - Dem som forst�r bin�r og dem som ikke g�r.
There are 10 kinds of people. Those who understand binary and those who 
don't.
Es gibt 10 Arten von Menschen. Die, die Bin�r verstehen,  bzw. die, die es 
nicht tuhen. 

0
Finn
11/14/2009 11:01:06 PM
dotnet.languages.csharp 1931 articles. 0 followers. Follow

3 Replies
1087 Views

Similar Articles

[PageSpeed] 26

"Finn Stampe Mikkelsen" <stampe@city.dk> wrote in message 
news:e4rwa5XZKHA.5608@TK2MSFTNGP05.phx.gbl...
> I'm looking for ways to encrypt data in my MDF database (SQL Express) and 
> i wonder if anybody had some examples on how to use it, especially in 
> conjunction with databound controls using dataset's..

   When you say "databound controls using datasets", I presume that the 
controls are bound to a dataset and that you transfer the contents of the 
dataset from/to the database using a DataAdapter. If you want the contents 
of the data encrypted inside the database (but not inside the dataset or 
when travelling from the client to the database), then you can encapsulate 
all the encryption and decryption inside stored procedures, and you can 
configure the DataAdapter to use those SPs, rather than the sql queries 
themselves.
    Inside the SPs, you would pass your data through the standard SQL Server 
2005 functions for encryption and decryption (EncryptByKey and DecryptByKey) 
before writing or reading the actual table.

    If you were using Sql Server 2008 I would suggest the much simpler 
avenue of using Transparent Data Encryption, but unfortunately this is not 
available in the Express version.

0
Alberto
11/15/2009 8:36:11 AM
"Alberto Poblacion" <earthling-quitaestoparacontestar@poblacion.org> skrev i 
meddelelsen news:#Slp46cZKHA.228@TK2MSFTNGP06.phx.gbl...
> "Finn Stampe Mikkelsen" <stampe@city.dk> wrote in message 
> news:e4rwa5XZKHA.5608@TK2MSFTNGP05.phx.gbl...
>> I'm looking for ways to encrypt data in my MDF database (SQL Express) and 
>> i wonder if anybody had some examples on how to use it, especially in 
>> conjunction with databound controls using dataset's..
>
>   When you say "databound controls using datasets", I presume that the 
> controls are bound to a dataset and that you transfer the contents of the 
> dataset from/to the database using a DataAdapter. If you want the contents 
> of the data encrypted inside the database (but not inside the dataset or 
> when travelling from the client to the database), then you can encapsulate 
> all the encryption and decryption inside stored procedures, and you can 
> configure the DataAdapter to use those SPs, rather than the sql queries 
> themselves.
>    Inside the SPs, you would pass your data through the standard SQL 
> Server 2005 functions for encryption and decryption (EncryptByKey and 
> DecryptByKey) before writing or reading the actual table.
>
>    If you were using Sql Server 2008 I would suggest the much simpler 
> avenue of using Transparent Data Encryption, but unfortunately this is not 
> available in the Express version.
>

Hi

You are quite correct in your assumption. I cannot use SQL 2008, so that is 
not an option.

Your solution using SP, would that not leave the data vulnerable?? I mean 
the SP would not itself be encrypted and would the possible hacker of the 
MDF file be able to decrypt the data by just using this SP??

Pls. excuse me, cause i'm not familiar with the server functions you 
mentioned, so i'm not sure how they are used. A code example would really 
help me see the light, so to speak.. ;-))

/Finn
-- 
Der er 10 slags mennesker - Dem som forst�r bin�r og dem som ikke g�r.
There are 10 kinds of people. Those who understand binary and those who 
don't.
Es gibt 10 Arten von Menschen. Die, die Bin�r verstehen,  bzw. die, die es 
nicht tuhen. 

0
Finn
11/15/2009 10:33:33 PM
"Finn Stampe Mikkelsen" <stampe@city.dk> wrote in message 
news:eQ2grOkZKHA.1592@TK2MSFTNGP06.phx.gbl...
> Your solution using SP, would that not leave the data vulnerable?? I mean 
> the SP would not itself be encrypted and would the possible hacker of the 
> MDF file be able to decrypt the data by just using this SP??

    No, the actual process is more complicated and requires to know the 
cryptography structure in Sql Server. The process is analogous to the 
following, but don't take my word as to the accuracy of every single minute 
detail: When installing the server, a Server Master Key is automatically 
generated and then protected in Windows using the DPAPI. This key is then 
used to protect a Database Master Key (which you have to generate manually, 
it does not exist by default in new databases). If someone were to grab a 
copy of your .mdf file, they would not be able to use the Database Master 
Key because it is encrypted with the Server Master Key.
    Inside your database, you create one ore more Symmetric or Asymmetric 
keys to protect your data. Typically, you would use Symmetric encryption for 
the data, because it is faster. You would protect the symmetric key by 
encrypting it with an asymmetric key (or a certificate), and the asymmetric 
key or certificate would be protected by the database master key. Therefore, 
all of your keys are protected, and cannot be retrieved from a copy of the 
mdf.

> Pls. excuse me, cause i'm not familiar with the server functions you 
> mentioned, so i'm not sure how they are used. A code example would really 
> help me see the light, so to speak.. ;-))

This is a sample of the kinds of things that you can do:

--Create the database master key (only needed once)
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'thePassword'

-- Create a certificate (only once)
CREATE CERTIFICATE MyCert
AUTHORIZATION NameOfUser
WITH SUBJECT = 'Name of certificate'
GO

-- Create a symmetric key (only once)
CREATE SYMMETRIC KEY MyKey
AUTHORIZATION NameOfUser
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert
GO

-- Insert encrypted data
-- You would encapsulate this inside a SP
OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert
 INSERT INTO MyTable VALUES
   (somevalues, EncryptByKey(Key_GUID('MyKey'),'Some Data'), 
someothervalues)
CLOSE ALL SYMMETRIC KEYS

-- Read and decrypt data
-- You would encapsulate this inside a SP
OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert
SELECT CONVERT(varchar,DecryptByKey(theColumn)) FROM MyTable
CLOSE ALL SYMMETRIC KEYS

0
Alberto
11/16/2009 11:26:13 AM
Reply:

Similar Artilces:

Can DPM be configured to log events to a remote syslog server?
I see that I can use Microsoft's MOM to publish alerts, but is there a way to send them to a syslog server? Thanks, Phillip Datagram's SyslogAgent can be used to forward Windows system events to a Syslog server. And it's free. http://syslogserver.com/syslogagent.html Daniel "Phillip Wendell" wrote: > I see that I can use Microsoft's MOM to publish alerts, but is there a > way to send them to a syslog server? Thanks, > > Phillip > . > Looks like a good solution. Thanks... ...

How do we let our SQL app 'relay' through Ex2003 SO2?
We have an app on a server 10.0.1.15 that uses Exchange to send out mail. Sometimes the mail is filtered out by the IMF and we find it in the UCE Archive. We want the mail from this IP to go through regardless. What's the best way to do this? Thanks, Neal exchange system manager. servers. your server name protocols SMTP Defaults SMTP Virtual Server properties access relay only the list below and add the IP address -- Nawar Aljanabi MCSE NT/2000/2003,MCSA 2003 +M,CCNA "Neal" wrote: > We have an app on a server 10.0.1.15 that uses Exchange to send out mail. > ...

Design advice SQL or QueryDef
I've built a form to dynamically create SQL statements. My original idea was to save the SQL Statements into a table ("tblCustomSQL") as a string. (Table has 2 fields, "Name", and "SQL".) I'm using an "INSERT" SQL statement to add records to tblCustomSQL. I'm running into trouble with nested quotation marks. If the custom SQL statement is formatted correctly, the quotations in the custom SQL interfere with the INSERT statement. I've tried encoding the SQL statement with markers (QUOTE - in place the multiple quotations), but de...

Im desperate to fix my fragmented memor...will advanced server save me with the 3 gb switch?
Im desperate to fix my fragmented memory, will advanced server save me with the 3 gb switch? The Situation: -Server=Windows 2000 Server sp4. -2.5 gb of RAM -Exchange=Enterprise Ed with all service packs and hotfixes. -Group Shield virus scanning Every month I recieve eventid 9582's errors, and then 12800 mail processing errors. THIS IS FREAKING KILLING ME AT WORK. Here is what I have tried. -Regedit ajustment to the heep. -ADSI ajustment to the IS (which oddly removes the errors from the event log, however MOM still detects low virtual memory errors). -sp3 (which claims to have fixed ...

Remote Server Question
Right customer wants to acces his win2k8 server remotely. IT is using a generic printer from the server. At the office the printer is on a network. Terminal Services are being used to access the server remotely. What needs to be done to get said printer working? -- Member - Liberal International This is doctor@nl2k.ab.ca Ici doctor@nl2k.ab.ca God, Queen and country! Never Satan President Republic! Beware AntiChrist rising! http://twitter.com/rootnl2k http://www.facebook.com/dyadallee UK Time for a Common Sense change vote Liberal Democrat / Alliance Whether "at the o...

Exchange 2003 server is a domain controller
Exchange 2003 on Win 2k Ent, 2 other DCs running Win 2003, single domain. The previous admin here made the exchange box a domain contoller. It's having some DNS problems looking for an old DC that doesn't exist anymore, log in issues etc. I'd like for it not to be a DC at all. The problem is that when I demote it from being a DC, no mail flows at all and OWC connections quit. Can someone explain to me why mail stops and what needs to happen for me to fix this? This is my last Win 2k box and I'm leary of trying to upgrade it in it's current condition. On Thu, 8 F...

WM_COPY message not being passed to controls deriving from CEdit within an ActiveX control
Hi, As stated in the subject, I have some custom edit controls classes in an ActiveX control. I can enter text and interact with the edit controls without any problems except in the case when I try to Copy/Paste/Cut/Clear through the key shortcuts Ctrl-C/V/X/B. Nothing happens when I press those key shortcuts, because the control itself never receives a WM_COPY/WM_PASTE/WM_CUT/WM_CLEAR message. Does anyone have any idea why these windows messages would be filtered out or simply not sent by the parent? Is there a more elegant solution that to override the OnKeyDown handler of the parent wind...

SBS 2003 Server Reinstall/Move
I have a client who has an SBS2003 server that (1) has an inappropriate server name (EAT-ME) and is (2) running out of disk space. Is there a way to do a clean install of SBS and move over the user accounts and email store to the new server of a different name (SRV-SBS). I want a clean server for the client because I doubt the integrity of the server. John, You asked this question once and you have answers in that thread. Starting a new thread with the same question won't change the answer.. If the answers provided were unclear or if you want to reply to them to add c...

Money 2005 Bug: Portfolio toolbar button loads blank page titled Portfolio Manager
Problem Description: Clicking on the Portfolio toolbar button loads a blank page that's titled Portfolio Manager. That's it! I can't see any of my open positions, etc... using this toolbar button. Frequency of problem: Always Happens Operating system / version: Microsoft Windows XP Professional - 5.1.2600 Computer manufacturer and model: Dell Computer Corporation, Dimension 8200 Processor model and speed: I586IV, 1994 MHz Memory (RAM): 523.276 MB The only input I can offer is that I have the same problem on one computer, but it works fine on the other. I converted from 2004 Delu...

News Feeds with Exchange 2003 Server
Hi I have a problem that Outlook clients cannot see News Feeds in the Public Folder/Internet Newsgroups after configuring newsfeeds on the Exchange server. The outlook clients can see the folder structure within Public Folder/Internet Newsgroups but there are no posts. Also when a new post is added using Outlook, the post does not get sent to the newsgroup on the internet, it only stays on the exchange server. Currently I'm using Outlook Express to subscribe and download newsfeeds and this works ok, so not a network or firewall problem. I would like to share some newsfeeds with...

installing Windows Server SP2 onto SBS 2003 SP1
I have SBS 2003 SP1. I have been keeping up with all the windows updates for this machine and would like to update the machine to SP2. I had heard that if you try to update the SBS 2003 SP1 server to SP2, you need to unload a lot of the updates issued after the SP2 was submitted. Is this true? Hi Carl, This hasn't been my experience. And there is no SBS 2003 SP2. It's Windows 2003 SP2 installed on top of SBS 2003 SP1 (which already has Win2003 SP1 installed). Run the SBS 2003 BPA prior to the install of Win2003 SP2 to make sure you don't have an issues ...

SQL Optimized Integration
We are implementing Dynamics GP and would like to use the SQL Optimized Integration option (instead of the Standard 'macro' Integration). The problem I am having is that we pass in 'discount' items in our orders by using negative Unit Price values. When I run the integration, I get an error message: DOC 1 ERROR: Unit price cannot be less than zero. Is there any way to modify the rules in the SQL Optimized Integration to allow negative Unit Price values? Thanks. try using negative quantities instead HS "Corwin" <Corwin@discussions.microsoft.com> wrote ...

CHtmlView show server busy dialog
Hi all, i use ChtmlView to display html, sometimes use clicked into a hyperlink, it showed a ServerBusy dialog. How to hide that dialog? ...

Extended CFileDialog and OCX-Controls
I want to extend the CFileDialog by some additional controls. All works fine with standard controls. But after add an OCX-Control the file dialog not appear. Inside DoModal the call ::GetOpenFileName(&m_ofn) have no effect. Anny ideas? Thank you Arnold It is not clear that OCX controls are supported, because the dialog template is not instantiated by MFC. OCX controls work in MFC dialogs because there is massive amounts of code in the MFC dialog-creation code to make this work. But the CFileDialog most likely does a ::CreateDialog call directly, so it can't work. I would suggest ...

Money 2005: Can't Get Support
I tried to open an on-line ticket for the upgrade problems I am having (2004-->2005), but the Microsoft site says "The Product ID number you entered is not supported in the country that you have selected. You may go back and choose another country or enter another Product ID number." I'm in the US. I selected "United States" and I purchased by d/l direct from Microsoft. BTW: Trying to report: 1) Cannot restore a backup unless I am connected to the internet. 2) When I started 2005, it converted from 2004 but lost quite a few transactions. 3) The first time the s/...

SFO causes High CPU utilization on SQL server
This recently started happening, and I am not sure why. I loaded SFO on two new laptops and attempted to go offline with each. The SQL Server is also the same server running CRM 1.2. When the synch starts on the client the SQL/CRM server instantly spikes to 100% and stays that way as the SFO client never finishes the synch process. Any ideas on a setting that might need to be changed or a patch, etc? Regards, Sean have you applied the outlook sfo client performance update which is available on support.microsoft.com/downloads? -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq...

Treeview Control Question
Hello, I am using a treeview Control in a form that has a tab control. The treeview control is located on one of the tabs. When the form loads it populates the treeview control(has checkboxes enabled on treeview), bolds certain text based on the SQL table it's pulling from, and then checks any items that were checked last time the record was saved. The problem that I am having is that when I move to another tab and go back to it, all of the bolds and checks are gone. I've tried redoing the query on tab control and page click actions as well as trying to set focus on the treeview ...

MS CRM 1.2 and MS SQL SERVER REPORTING SERVICES
CAN MS CRM 1.2 CO-EXIST WITH MS SQL SERVER 2000 REPORTING SERVICES? WE CAN ONLY GET EITHER CRM OR REPORT SERVICES TO WORK NOT BOTH. THANKS. Yes, they can co-exist. Also, please DON'T SHOUT. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Fri, 8 Jul 2005 16:08:02 -0700, HEC <HEC@discussions.microsoft.com> wrote: CAN MS CRM 1.2 CO-EXIST WITH MS SQL SERVER 2000 REPORTING SERVICES? WE CAN ONLY GET EITHER CRM OR REPORT SERVICES TO WORK NOT BOTH. THANKS. Could you please enlighten us Matt? If one has CRM 1.2 in...

Multline Tab Control with TCS_MULTILINE always empty
Hi ... I've been working with tab controls for a while now already, but this time I need to have a tab control that has multiple lines due to space contraints. I discovered the TCS_MULTILINE (Multiline) property and thought that this would solve my problem. Well, for some reason it's not working for me and I have no clue why. Basically, as soon as I set the type to multiline, the tabs themselves don't have any text in them (they're basically empty) and the control is not even multiline. Is there something that I am doing wrong? I tried clicking the option through the...

Bank Transaction Entry -- SQL Error
Hello, When our user are attempting to enter a Bank Transaction Entry, they are getting the following error when they hit "Post" [Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER] Error converting data type int to smallint. When that error message is closed, the following pops up: The stored procedure aagUpdateBrowseTypeForBRVoidedTRX returned the following results: DBMS: 8114, Microsoft Dynamics GP: 8114 FYI, we are on SQL Server SP3. Anyone seen this kind of error before? Any suggestions? Thanks More important is to know what version, build number of GP you are running...

Tab Control Subform SetFocus
My main form has a tab control which has a subform embedded in datasheet view. The main form (frmNewProd) has a combo box (cboShift). I want to make a selection in the combo box, hit <enter> and set the focus to the first field (WorkstationID) of the first record in the empty subform (sfmProdOp) which is embedded in the tab control (tabProdDetails). I have tried attaching the following code to the AfterUpdate event of the combo box on the main form: Private Sub cboShift_AfterUpdate() Me!sfmProdOp.SetFocus Me!sfmProdOp.Form!WorkstationID.SetFocus End Sub but it doesn't go ...

How to export a portfolio with Money 2005, please?
I have accounts with more than one mutual fund company and have set them up for online access in Money 2005. I'd like to send a financial planner a snapshot of my "portfolio" - basically fund name and the number of shares in a spreadsheet / csv file, so that they can plug it into their software to analyze my holdings. I've spent some time exploring Money (including the Export choice which appears to export a single account (but all transactions)) but can't seem to find a way to do it. Is what I'm trying to do possible with Money 2005? Thank you, Russell ...

Do I need Exchange Server ?
Heres my question: I have 2 computers. We'll call them Computer A and Computer B. My Main computer is Computer A and I have a few programs installed on it that I want Computer B to run. Mainly my MS Office Outlook. I want Computer B to execute MS Outlook from my Computer A through the LAN Network. I want Computer B to send and receive emails. Then when I open the program on computer A all the emails send and received will show up on computer A. Is this possible? Heres the low down. I run Computer A to build my web site. While the wifey handles the email/customer serive and orders....

Exchange 5.5 : anyone remember how to specify the exact domain controller to use???
Hello, We still have a client using a single Exchange 5.5 server for a specific application. They're going to replace it but not until next year. In the meantime, I need to force it to use one specific domain controller because the others have been security hardened. Does anyone remember please how to tell Ex 5.5 which DC to use all the time? Thanks, - Alan. You might try separating the Exchange server and specific domain controller into their own AD site. -- Ed Crowley MVP "There are seldom good technological solutions to behavioral problems." .. &quo...

Terminal server login environment
Hi Everyone, Having problems with GP and terminal server login. When the user logins I want it to bypass the desktop and go right into GP. I have tried creating a script and it is still not working. anyone have any ideas? thanks in advance Nic I've done this once before, and I will have to find my notes on how to do it. But I do remember it involving a macro. I'll post when I find it. "Nic" <anonymous@discussions.microsoft.com> wrote in message news:2e60a01c46b69$2744e0a0$a401280a@phx.gbl... > Hi Everyone, > > Having problems with GP and terminal...