A Bit Of Beginner Confusion About SQL Server 2005 Encryption
Dec 26, 2007
Hi,
I have studied a variety of online documents explaining built-in SQL Server 2005 encryption, and I'm a bit confused. Every encryption approach, it seems, ultimately replies upon a password that must be provided with queries to access the data. As an application developer, it brings up the obvious question: how should that password be provided? If I build the password into my applications, then it will no longer be secure. On the other hand, I can't possibly expect my users to provide a password every time they perform an action that requires unencrypting data. If I give that password out to 50 users, the password will become public information quickly, I am sure. We will also have to alter the password regularly. Plus several of my applications run as windows services, in which case the user (meaning the windows user under which the service runs) won't be around to type in password.
I have a better solution in mind. Is there an option to limit access to symmetric keys by windows identity? As a best-standards-abiding coder, all of my sql server access is done via Windows Authentication instead of SQL Server Authentication. Why not make it so that myorgjoe and myorgsally can access the symmetric key for a particular column, but nobody else? This way there is still a password involved, but it is now moved further up the application layers; it is the windows password that the user originally used to log into their machine to run the application.
Is there a way to make it so that access to symmetric keys (or asymmetric keys which encrypt symmetric keys) is decided solely on the basis of windows user identity?
I am an absolute beginner with SQL Server, and I have now developed an app that needs to store, read, and search for saved data. Basically, my program will have to search through the databse to find a particular URL match to a string I supply, and then retrieve the Username and Password data that is associated with that URL, storing those to strings.
My problem is, I've learned how to create a database and tables in the IDE, but all of the examples on the web that I've found only talk about using SQL Server with Visual Basic 2005 in uses such as databinding to controls and displaying the database in datagrids and such on the form. I need help with how to interact with the database in code, seeing as the user of the program will likely never actually see the database contents- the data will be for the program's use only.
So, I was wondering if anyone could provide/guide me to some helpful information on interacting with SQL databases through code in VB Express 2005.
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 am fairly new to Visual Studio 2005 (using C#) and am writing an application for the company I work in. I have got a really nice functioning form working and am now planning what to do about the massive database I need to create (I don't think it will ever get to the 4GB limitation of SQL Server 2005 Express Edition).
Can someone please help with some simple questions please?
1: I am decoding a specific file format and extracting the information for the main database. Fortunately, each record in the data file I am decoding has unique identity codes (so this will be great for the ID of each entry). Because the software has to do this by itself (the encoded file contains more than 1,120,000 records which will shrink to about 30,000 records, all different sizes once decoded). Can each record in a SQL server database contain a different number of elements? If so, what is this known as (I have read a little about jagged arrays - is there a similar thing in SQL server databases)?
2: As the program will decode the file and assemble the database, can I make my program create the database from scratch and manipulate the database rather than me having to define a database for my program to fill? If so, where can I find some info on doing this?
3: The database file will be stored on our office network and has to be accessible to more than one person at a time (in this case, a maximum of 6 users - well within the 25 users my book says is possible with SSE2005). Is it more complex to develop multi-user access to the database?
Just in case you wonder what books I have got, they are "Introducing Microsoft SQL Server 2005 for Developers" and "Getting Started with SQL Server 2005 Express Edition".
I am a total beginner to Pocket PC, and a 2-month beginner to SQL Server 2005 and Visual Studio 2005. I made a database Pillbox.mdf, and wrote a Windows Forms program to access it. It's really easy. I just added a Data Source, chose tables and Stored Procedures, and dragged them onto my forms. It automatically created adapter classes and a DataGrid for me.
Next, I wanted to do the same thing for Pocket PC. I did the same drag and drop thing, and was happy to see the empty DataGrid in the emulator (design-time) form. From here on, trouble. When I ran it, it said it could not connect.
So some questions:
1) Does my connection string have to change? It has 3 parts: machine name, database name, and security spec. It specifies my machine name. Is that ok? Is it known by the Pocket PC, or do I have to specify http something? Does my machine have to change any settings to become a "server"? It's just a standalone PC with no network. I understand the emulator chooses to pretend that it's not "on" my computer. Is the database name known by the Pocket PC? (Same type of question.) And is Windows authentication ok on a Pocket PC? Or must I create a name/password pair on SQL server?
2) When I went to modify my connection string, I noticed a setting to specify the Data source. For SQL Server 2005, it asks me for a dbo, but for SQL Server Mobile, it asks me for a *.sdf file. What type of file is this? Is there a way to generate this from my other database? Is it not possible to have the the Pocket PC app point to the same database as the Windows app? Oh... does the sdf represent the Pocket PC's local database? If so, then my question is just how to populate it. But I will also have to update the main database at some point. I guess I'm confused about how Pocket PC's are supposed to work.
3) Do I have to deploy SQL CE or something? I think I read that Visual Studio does it automatically. It's a bit hard reading blogs because many comments talk about things that are now contained in VS. One blog was talking about 7 dlls that need to be downloaded to the Pocket PC.
4) My application just has to get a table, store it on the Pocket PC, and allow the user to modify and update records. The bandwidth should be low, as the table is small and updates should not be frequent. Is this a reasonable design? What I am asking is if it's ok to have direct database access, or if I should package up the data in some custom format, and use a Web service to pass the data back and forth. I have read and understood articles why you should not pass a Dataset in a web service, but the point was just that web services can be interoperable, so why spoil that with MS-only Datasets. In my case, I just want to get this simple app up and running quickly.
I am trying to install Visual Studio 2005, but before that I need install MS SQL Server for my database, what kind of MS SQL server I need install, how about "MS SQL 2005 Express Edition"? Or I have to install "MS SQL 2005"?
I have a central database server that is runnning on SQL 2005 standard edition and Windows server 2003 standard as OS.
I realise that I can use SQL statements to encrypt and decrypt the data inside the standard SQL.
However, how do I read and write the data via an web application coded in C#.net and is also running on the same machine?
Another issue is, I need to replicate some of the data in this SQL standard over to a SQL mobile running on a mobile device running on Windows CE 5.0.
The mobile device also needs to read and write data to the encrypted data via a C#.net application.
Question is, with all these requirements to be met, can I use AES? I know that AES is not available on Windows XP and Windows Server 2000 and I cant find AES in the .net compact framework.
how do i go about ensuring security? how do I ensure that the symmetric key is the same both on the SQL standard and SQL mobile?
I am having a problem with some code I have in a DLL that is running in SQL Server 2005. I am trying to get some RSA encryption and decryption. The encrypt code runs in SAFE mode without a problem. The decrypt code gets and error:
Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "March_CryptoDecrypt": System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.KeyContainerPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Security.Cryptography.RSACryptoServiceProvider.ImportParameters(RSAParameters parameters) at System.Security.Cryptography.RSA.FromXmlString(String xmlString) at Crypto.DoCrypto.Decrypt(String P_text, String P_privateKey) at SQLServerCrypto.Decrypt(SqlString P_text, SqlString P_privateKey)
Here is the decrypt code:
static public string Decrypt(string P_text, string P_privateKey) { string retStr; string encryptedBlock = ""; string localTextStr = P_text; int numberOfBlocks;
RSACryptoServiceProvider rsaProvider = new RSACryptoServiceProvider();
rsaProvider.FromXmlString(P_privateKey); Queue<string> encryptedBlocks = new Queue<string>();
If I understand all the posts/documentation correctly am I correct in saying that sql server will not send a symmetric key outside of database.
For Eg can I use ADO.Net to get the key from database into a C# application to do encryption/decryption in the C# application outside of database. I want the C# application to be able to encrypt/decrypt data using .Net cryptography api's but use sql server as key store in addition to encryption/decryption.
I store data in an .mdf file (I use SQL server 2005), because this way it's easier to move the application from one machine to another.
I've faced a problem with the encryption of the database.
Is there a possibility/way to encrypt a database file so that, if someone else finds/copies the mdf, he/she won't be able to read it.
I thought about encrypting the string values of the tables itself and decode them inside the application and encide when Inserting, but why inventing somehing that might already exist.
Hi everyone! I have a problem and I was hoping someone could help me with it.
Here's my scenario: I have to access to an intermediate SQL Server 2005 database, which I cannot change or alter. In this database is information that a I need to retrieve and put in our website database. One item of information is a persons SSN which is stored in a varbinary field and encrypted using a certificate.
In my Data Flow task which processes this information I am using an Ole Db Source to retrieve the information with the SQL Script:
SELECT CAST(DecryptByCert(Cert_ID('Certificate_Name'), [IntermediateDB].[SSN]) AS VARCHAR) As SSN FROM [dbo].[IntermediateDB].[SSN]
BTW, This script runs fine from within SQL Server Management Studio. It decrypts the SSN to the appropriate value. However, when I run it in SSIS, I receive a truncation error which is no small surprise b/c the SSN value is in a large binary format. I.e:
It goes on further, but for the sake of brevity and my own sanity I thought I should truncate it.
It seems like SSIS's use of the certificate is failing (although I don't get an error for that) and is simply pulling the encrypted SSN from the intermediate table.
So my first question would be, is this actually what's going awry? And secondly, is there a way to fix this without touching the source database??
Thanks! I greatly appreciate it! And Happy Holidays!
I read a couple of articles related to encryption topic in this forum and I feel that's really helpful I don't know if anyone has some knoeledge about the encryption issues in replication and clustering environment. I read some documents from Microsoft web site that explains how to move an encrypted database from its original server to another new server instance. That cause a lot manual works, if the database master key has been encrypted by the original service master key and you still want to enjoy the auto-open feature in the new environment. As we know the Microsoft SQL Server 2005 has a hierarchy encryption key structure and its top level service master key is really service oriented. For what kind of mechanism or set up, Microsoft makes their encrypted database working smoothly and automatically in a clustered and replicated infrastructure. Is that possible to sacrifice the security a bit by dump the service master key for database master key and make database more portable? I search the web site all the way, but couldn't find the related topic. Anybody has a good idea or experience to share?
hello everyone, I am kind of confused with MS SQL 2005. I have friends who have been developing with MS SQL. They told me that in order to have a MS SQL Server, one need to have a PDC.
This is really a problem since I am behind a Unix box acting as a firewall/gateway. AFAIK, to install a PDC means that DHCP comes with it and DNS. This is really a problem...
Is it possible to have a MS SQL server without having to create a PDC? If so, how do I connect to my database on an MS SQL server. I develop in VB. I would appreciate any help/tips... TIA :D
I faced the problem trying to adjust ssl encryption in ms sql server 2005. I've completed all steps from this article: http://msdn2.microsoft.com/en-us/library/ms191192.aspx
But when I'm trying to restart sql server - it fails to start.
Here the error messages I got:
1. Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
2. TDSSNIClient initialization failed with error 0x80092004, status code 0x80.
3. TDSSNIClient initialization failed with error 0x80092004, status code 0x1.
4. Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
5. SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
SQL Server is installed on Windows 2003 Server OS running computer.
I use certificate created by means stand-alone Certificate Authority that appeared in Administration Tools on that computer after I installed Certificate Services.
I guess the reason is in wrong certificate parameters I set while requesting.
How can I determine correct certificate parameters? Does anyone know?
If I buy MS SQL 2005 Standard edition and install on my production machine, can I later uninstall it and then install it again on ANOTHER MACHINE? (so only one copy is used)
I'm a little confused over the maximum CPU count supported by SQL 2005 Standard Edition (this particular edition supports four CPUs).
Does the figure refer to four physical CPUs regardless of whether they are dual-cored or hyperthreaded, or does the figure refer to the number of logical CPUs available to the OS?
Let me cut to the chase - if I purchase a server containing four dual-core CPUs and install SQL Server 2005 Standard, will SQL Server see the eight CPUs and utilise a maximum of four of these, or will it be able to use all eight (because there are actually only four physical CPUs)?
I'm a little confused over the maximum CPU count supported by SQL 2005 Standard Edition (this particular edition supports four CPUs).
Does the figure refer to four physical CPUs regardless of whether they are dual-cored or hyperthreaded, or does the figure refer to the number of logical CPUs available to the OS?
Let me cut to the chase - if I purchase a server containing four dual-core CPUs and install SQL Server 2005 Standard, will SQL Server see the eight CPUs and utilise a maximum of four of these, or will it be able to use all eight (because there are actually only four physical CPUs)?
Can my program with SQL database run correctly on another com using win xp home edition, with only C# 2005 EE installed? Is it compulsory for every table in a database to have a primary key? Can I extract a data from a specific cell in a database table(click button get data)? How do I go about doing it (the simplest n straight forward method)?
I want to install SQLExp2K5 with Advanced Services on a standalone basis on my Dell Inspiron XPS laptop (Win XP sp2, .NET 2.0; 2gb RAM; 8mb broadband; hardware firewall/router) so that I can learn SQL/VB/VWD, and want to use SQL E 2K5 without internet connectivity (at least until I know what the hell I'm doing!)
Really confused as to what component(s), and services I should/should not install - so I've listed my questions below:
1. Service Account: As I'm not on / dont have a domain, i presume using a domain user account is not an option for me. Should I use the Local Service account ? If so, what are/can be the issues in doing so?
2. Authentication Mode: Is Windows Authentication the one to use for a standalone install?; how would I enable the "sa" account and set an "sa" password? In fact, do I even need an"sa" account / password for a standalone, non internet connected install?
3. Components at install: Is it OK to install all components first off?
4. IIS: Is it possible to install IIS and not allow it access to the internet (or is this a stupid question -bearing in mind I dont really know what IIS is/does)
5: Business Intelligence Development Suite: How do I add BIDS once (IF) I manage to install SQLExp2K5-Adv.Ser,?
6. SQLExp2K5 Books on-line: Am I right in thinking that I should install this after installing SQLExp2K5?
7: Visual Basic Express/Visual Web Developer Express: Do these need to be configured seperately to work with SQLExp2K5?
Sorry about the length of this post, but have tried to be as clear as possible. Also apologise in advance for silly/stupid questions :)
HelloI am running a SP from the SQL Server Agent, the job has one step thatlooks like this.exec q_spr_inlevextsystemThis job fails with the following messageJob 'AutoInlev' : Step 1, 'Run the SP q_spr_inlevextsystem' : BeganExecuting 2004-04-05 09:00:00output---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------(null)Starting copy...(null)1 rows copied.Network packet size (bytes): 4096Clock Time (ms.): total 1(null)(0 rows(s) affected)Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE22001]Msg 3621, Sev 16: The statement has been terminated. [SQLSTATE 01000]however if I run that exact commandline from queryanalyzer it worksperfectly.What does SQL Server Agent do different from query analyzer? this hasme totally stumped.regardsMatt
i'm quite confused from SQL Server 2005 security permission granting. Could you be so kind and post some generic, compact, web source for this topic, please?
caution: this is not doubt a stupid newbie question...
In creating vs.net 2005 website, I can add a sql database to my project and a mdf file is created. I can create data providers against this file, etc, just as though it were a database in a sql server instance. I can deploy this dbf file to my finished web site.
Also, I can attach to a running instance of sql server 2005 express, and do exactly the same thing.
I can also take my mdf file created in step 1 above, and attach it to a running instance of sql server express.
Now, I have delt with access databases, and sql server 2000 databases, so this dual nature of sql server 2005 express confuses me a little.
Why would I ever need to use a server instance of sql server 2005 when I can use a file based data file in my web apps? Is there an advantage to one or the other?
I had a thought that when using the file based method, I was actually still using the server based stuff, which would explain why the sql server express notification bubble pops up when I debug on the dev machine.
In any case could someone explain the difference and should I install sql server 2005 express on my deployment server?
I trust you'll bear with an SQL Server newbie with what may seem a rather inane request. I am designing a web app in Web Designer 2005 Express with SQL Server Express. Unfortunately, I'm finding a little confusing with some of the data types when designing tables. I have tried to find information on the various Microsoft sites (general site, MSDN, here) and while I found one document that had a table comparing data types in different implementations of SQL, it wasn't at all helpful. Most of my confusion is with the various string and char types; the numeric types seem pretty straight forward for the most part. However, it might be helpful to know the difference between money and smallmoney/datetime and smalldatetime, particularly space/size information and formatting options (unless the latter is up to the interface). It would also be helpful to know which string/char types correspond to any counterparts they might have in, for instance, Access (with which I am already quite exprienced). Or any particular quirks or idiosyncracies they might have. I don't expect anyone to write a full tutorial, but if someone could point me in the direction of a good online doc, it would be most appreciated. You might well ask, why not use Access databases? I would answer...I like to learn new stuff! Thanks much.
Hi all I am trying to install SQL server 2000a I install per the instructions but when I go to plesk to create a SQL database I get the following error message: Unable to create database: Unable to create database-dependent object:Database Microsoft SQL server is not running. I am running a Windows 2003 duel processor server. I would also like to know if I can upgrade to service pack 3a from 2000a or do I need to install service pack 2 first. Also should I install service pack 4? Thanks Cruiser859
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. I€™m also not clear how to use this in SQL (not sure it matters if it€™s 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?
It is clear to me that in order to be able to use certain SSIS components (for example the Excel jet provider) I must launch my packages using the 32bit DTEXEC located at Program Files (x86)Microsoft SQL Server90DTSBinn. However, when I do this it seems that there are other components of the package that no longer work as expected.
To test this I have created a simple package with two tasks (Run64BitRuntime is set to False): 1. Data flow task importing data from Excel 2. Execute SQL Task which does a simple select (select 1) from a Native OLE DB SQL data source (same SQL Server on which packages are stored). This task contains no input or output parameter.
When I try to execute the package using the 64bit DTEXEC, task 1 fails with the following error (as expected): Code: 0xC0202009 Source: connection1 Connection manager "SourceConnectionExcel" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
When I execute the package with the 32bit DTEXEC, task 2 fails with the following error Code: 0xC002F210 Source: Execute SQL Task Execute SQL Task Description: Executing the query "" failed with the following error: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Now here is the confusing part: When I change task 2 to use the .Net provider instead of the OLE DB the package works fine. According to the MS documentation, both of these providers are supported on 32 and 64 bit so am I missing something? One more thing to note: before I was able to use the 32 bit DTEXEC I had to re-register it as described in this KB article: http://support.microsoft.com/kb/919224
