Oct 14, 2009: ADO.NET Tips & Data Encryption

Dennis Parks
SQL Server / ADO.NET Tips and Tricks and Best Practices

Dennis will cover a variety of SQL Server behaviors, anomalies, and features, including bounded connections and snap-shot isolation gotcha’s, as well as some ADO.NET best practices for enterprise applications. He will also demonstrate how to create C# code that is fault tolerant to connectivity errors.

Dennis currently works as a Senior Software Engineer at Kryptiq in Hillsboro, Oregon. He has worked as an independent consultant providing businesses in the Portland area with contract programming, IT services, custom software and database solutions for over 30 years.

Dennis graduated from Oregon State with a Bachelor of Science in computer science and holds the following Microsoft certifications: MCSE, MCSD, MCDBA, MCAD, and MCPD. He has been working with SQL Server since 1996, and is President of Alpha Development & Support, Inc.

Thomas Mahoney:
Case Study: Meeting PCI Requirements for Sensitive Data Encryption

The company needed to be certified to comply with the PCI (Payment Card Industry) requirements attesting they were able to secure (encrypt) credit card. The credit card numbers were stored in over 300 tables, using four different encryption schemes (third party, SQL Server functions, and a couple of variations on the .NET encryption capabilities).

The new system needed to use the current T-SQL encryption function (changes would not be allowed to the function signature). All existing stored procedures and .NET application code that called the function must continue to function correctly and without error. Also, there was some data outside the PCI requirements that also was encrypted using that same function. The non-PCI encrypted data must continue to be encrypted and decrypted using the same function and the existing processes.

The new system would have to meet all of the PCI requirements, and all sensitive data must be converted within three months without interrupting production. There must be a means to change to a new encryption key each year without losing the ability to decrypt any data already encrypted.

Thomas will discuss how they did this on time and within budget. He will present the approach used to convert almost a billion credit card numbers without losing any or degrading server performance. Thomas will present:

  • The problem (in more detail)
  • The options presented
  • The solution selected and why (a centralized one)
  • How the conversion was accomplished (via dynamic scripts that generated scripts used to convert – only the dynamic TSQL code generating script had to be coded)
  • An overview of the architecture (SQL Server, CLR, and Web Service)
  • Thomas will also present some statistics and show how the solution was 90% SQL based (SQL Server 2005), 5% SSIS, and 5% .NET (CLR and Web Service).

    Thomas Mahoney is currently employed by Genesis Financial Solutions as a Database Developer and Development DBA. He maintains the development environment, resolves production issues, builds SSIS packages, and mentors developers. Tom has been in the computer industry for over 29 years. Started off as a COBOL/FORTAN developer as a Captain in the USAF where he built logistic systems that transported information over the ARPANET (Advanced Research Projects Agency Network).

    Thomas became data centric while developing General Electric’s Shop Floor Control of a Manufacturing Planning and Control System. He worked with Sybase for seven years (including the PS2 version) and started with MS SQL Server back when it first came out. He has taught data architecture, data modeling, and database theory at several universities. Thomas holds a BBA (MIS) degree from Southwest Texas State University and a MBA from Russell Sage in New York.

    This entry was posted in Announcements. Bookmark the permalink.