|
Backing Up Service Master Keys |
|
|
By host on
7/23/2008 6:41 AM
|
|
|
When an instance of SQL Server 2005 is installed a Service Master Key is created. This Service Master Key is the top unit in the encryption key management hierarchy for SQL Server. Its purpose is to encrypt the certificates, symmetric keys and asymmetric keys that are employed on any/all databases that reside within that instance.
The Service Master Key is based off of the service account credentials as well as the machine key from the Windows Data Protection API. Without it, all subsequent keys would not be decipherable; thus rendering the data, in which the encryption was implemented, forever a mystery.
Regular database backups are a best practice that most DBAs execute. This practice allows the database to be restored from a specific point in time in the event of data loss or other nightmare scenarios. If encryption is implemented a simple database backup will not suffice for recovery. The Service Master Key is not included in the standard database backup process. Backing up the Ser ...
|
 |
|
|
More...
|
|
|
Exploration of Encryption |
|
|
By host on
7/18/2008 7:02 AM
|
|
|
Encryption is a valuable tool in protecting sensitive data from those who are not authorized to view it. It is often stated that cryptography is the last line of defense in the myriad of security technology and methods that can be applied to any given system. With encryption comes a collection of new problems and challenges that the developer and DBA will have to face; but certainly these issues pale in comparison to the consequences of disclosing sensitive data to unauthorized entities that may have a less than honorable intent for gaining access to the data.
Throughout the internet there are countless examples of how encryption can be implemented in ASP.NET (and other languages such as PHP). I have employed these methods in past projects to obfuscate connection strings in the web.config as well as passing sensitive data to the database.
There are also many examples of how encryption can be implemented in SQL Server; although not quite to the volume of our UI friends. A spectacular exam ...
|
|
|
|
|
More...
|
|
|
A Brief Plug for IndyTechFest 2008 |
|
|
By host on
7/12/2008 7:11 PM
|
|
|
IndyTechFest 2008 has now entered the marketing stage for the event.
For those who are unaware of IndyTechFest, it is a technical conference held in Indianapolis and is hosted by IndyPASS (Indianapolis Professional Association for SQL Server) and IndyNDA (Indianapolis .NET Developer’s Association). The inaugural event was held in October of 2007. We provided over 20 sessions. The attendance was right at the cusp of 400 and the entire event exceeded the expectations of all who supported our efforts.
This year we have over 30 sessions offered in one day. The topics being covered are .NET, SharePoint, SQL Server and other related topics. We are very proud of the caliber of speakers that will be attending; many are Microsoft MVPs, all are experts in their resp ...
|
|
|
|
|
More...
|
|
|
Database Nation Review |
|
|
By host on
7/5/2008 3:57 PM
|
|
|
I have just completed a reading of “Database Nation” by Simson Garfinkel. This book first grabbed my attention because of my interest in data confidentiality and the apparent database focus implied by the book title. The glaring eye peering through the keyhole on the cover also did a lot to grab my attention.
As I began to read the book, I found that the exploration into the history of the social security number very interesting. The further exploration into biometrics was a great follow up. I have often wondered how biometrics could be compromised short of gruesome mutilation. This section of the book reminded me that this information is converted into data and stored which makes it equally vulnerab ...
|
|
|
|
|
More...
|
|
|
Honeycombing Revisted |
|
|
By host on
6/30/2008 8:25 AM
|
|
|
|
On June 2nd, I wrote a blog entry on the subject of honeycombing a database. In that entry, I discussed the concept of honeycombing and provided an option that could be executed for SQL Server 2000 or 2005 databases.
The challenge with SQL Server 2000 and 2005 databases is that there is not a great method in which to capture information about a SELECT event. Other DML events (UPDATE, INSERT and DELETE) could be captured with a trigger; but unfortunately SELECT was mysteriously missing as an event in which a trigger could respond. The option provided was admittedly a rigged solution that should be very carefully explored before implementing.
I also mentioned that I had not yet explored the features of SQL Server 2008 at that time to see if there was a solution. Since then, I spent some time with
|
|
|
More...
|
|
|
Considering the Confidentiality of Date of Birth |
|
|
By host on
6/22/2008 7:54 PM
|
|
|
In the world of managing data there are some pieces of data that obviously demand special attention when considering confidentiality like social security numbers, credit card numbers and driver license numbers. There are also others that fall into that gray area that are either debated by those who must identify confidential data or not even considered.
The date of birth is one of those data elements that reside in that gray area. Alone, this element of data contains no real value that would raise any concerns of confidentiality. As many say who do not want special attention on their special day say: “It is just another day.” When combined with other elements of data such as full name and address the date of birth becomes very confidential.
The date of birth is something that is commonly used in combination with other data as confirmation of identity. With a name, address and date of birth a person can obtain a certified copy of a birth certificate. When a certified ...
|
|
|
|
|
More...
|
|
|
Finding Similar Columns In A Haystack |
|
|
By host on
6/15/2008 10:27 PM
|
|
|
|
Large enterprise databases will often have columns that are repeated in many tables throughout the schema. These columns, while containing unique data, may have identical properties. Maintenance of these columns can be challenging, especially if you are not the one who is as intimately familiar with the database schema as the original architect might be.
The brief case study below illuminates how this can be challenging:
Joe Deebeay maintains an enterprise database that contains over 200 tables. Joe’s manager has stated that the user interface of the application will begin to pass all of the zip codes with ten characters.
In the original design of this database’s schema, the original architect, who was not Joe, determined that the data type of the zip code fields should be varchar(5). This size accommodates the standard zip code in the format of “46204”. The new format will require Joe to increase the column’s size to varchar(10) which accommodates t ...
|
|
|
|
|
More...
|
|
|
Considerations for VARCHAR and NVARCHAR |
|
|
By host on
6/6/2008 11:47 AM
|
|
|
If you peruse quotes and philosophies of very successful people it will not take long to see that revisiting the basics of a discipline is something that is critical to excelling; thus, I thought that it would be a good idea to comment on the varchar and nvarchar data types.
According to SQL Server 2005 Books Online the varchar(n) data type is described as:
“Variable-length, non-Unicode character data. n can be a value from 1 through 8,000.”
According to SQL Server 2005 Books Online the nvarchar(n) data type is described as:
“Variable-length Unicode character data. n can be a value from 1 through 4,000.”
The “n” referred to above is the defined ...
|
|
|
|
|
More...
|
|
|
Honeycombing the Database |
|
|
By host on
6/2/2008 8:12 PM
|
|
|
|
Over the past few months I have been reading a book of the title “Cryptography in the Database: The Last Line of Defense” by Kevin Kenan. I would certainly recommend this book; although I have yet to complete reading it in its entirety. With all of the other things that are in mid-air in this juggling act that we call living I have found that my reading has turned into multiple short sprints rather than the long marathons of days past.
In this book a concept called “honeycombing” is very briefly introduced. This concept is the placement of tables within a database that contains seemingly highly confidential data. These tables have no purpose other than fulfilling the role of a decoy. When a data snooper happens upon these tables and performs a ...
|
|
|
|
|
More...
|
|
|
Joining Table-Valued Functions |
|
|
By host on
5/29/2008 8:28 AM
|
|
|
|
In a relational database the use of JOIN is invaluable when combining data in one table to another. Through stored procedures the ability to use JOIN to combine physical tables to views, temporary tables, derived tables and table variables is equally important in our realm of data retrieval activities.
In SQL Server 2000, the table-valued function was introduced. The table-valued function provides a user defined function the ability to return a table object. The ability to combine the results of a table-valued function has been available through the use of JOIN; but presented some challenges when the argument that is to be passed is a value that changes for each record of the parent table. The following statement will result in a syntax error:
SELECT
&nbs ...
|
|
|
|
|
More...
|
|