Damn it! Now you tell me, SSMS2016 & PowerShell don’t mix

PS_BreaksI’ve been told by friends to stop being the early adopter for technology thingies.  I’m on step 4 of the 12 step Technology Anonymous (TA) program but, I still help trying out the latest database thing, in this case, SQL Server Management Studio 2016.  Yea… should have waited, most of my existing PowerSell scripts that backup and sync stuff across my development and staged areas just kinda stopped.  I’d like to thank Drew for his post about this! Helped me get my SQLFoo back in the groove again.  I promise I will not miss another TA meeting.

SQL Server Column encryption

LockItThere are times when you need to encrypt just a column in a database table.  Most posts on this topic relate to a password that needs to be encrypted.  This example highlights encrypting Protected Health Information also known as PHI.  PHI fields can include medical id, beneficiary id, Medicaid id or address information.   Private information PI can include social security numbers, driver license id or inmate id.   Both PHI and PI information can and should be encrypted.

Some are fans of encrypting within the application (C#, Python, Ruby, .NET) others prefer to encrypt at the database engine level.  Encryption requires processing cycles at application, database or both levels, so choose carefully where you encrypt/de-encrypt.  This example will show how to perform encryption at the database level for a single column.

Encryption algorithms vary in level of encryption but basically the deeper the encryption the more processing cycles you will use.   In this example, we will use the RSA_2048 encryption algorithm.  This example will also be using an Asymmetric key (two parts) one part of the key embedded in the database, the other part external to the database and know to the developer.    If the PHI/PI data is copied out of the database in its encrypted form it can NOT be decoded because the database key is not available to whoever got the data.    If somehow the person has access to the database they can NOT decode the data without the developer key.   This protects the PHI/PI on either side of the database wall.  This type of encryption is not bulletproof if someone has access to the database and gets the developer key they could decode the PHI/PI.

SQL developers will frequently use Stored Procedures (SP) to code up access to the database for the applications.  If the developer key is used in any of those SP’s it becomes a risk because another database user could use this key to decode PHI/PI.   One way around this risk is to have the developer key passed as a parameter to the SP from the application.  This means someone would need to have both application and database access to decode.

Create an Asymmetric Key in the database

The first step is to create the Asym key in the database.  Connect to your server and login with a user who has admin access to the database.  Create a developer key that is only going to be known to the developer.

Create a Query to show how the Encryption Works

To show how the encryption works we will do a query and encrypt the Column Beneficiary ID which is considered to be PHI.  Please notice that the developer key is not needed in order to encrypt data.

Line 1: The database key we created is turned into an integer variable which will be used to encrypt the Beneficiary ID
Line 6: If we displayed this column it would be bad because this is the PHI column
Line 7: This is where the database key, now in @KEYID, will be used to encrypt the Beneficiary id

Encrypted Query Results

The results from this query are shown below, no PHI information is being displayed.

EncryptQuery

Notice that the (Encoded_BeneID) is an encrypted column and it is really long.  This de-identifies the Beneficiary ID which is considered PHI.   If this result were to be inserted into a table or converted to a file and sent to someone, it would still be secure.

Decrypt the Encrypted data

Making the assumption here that the encrypted data was loaded into a table named called BenePopulation we need to decrypt the Beneficiary ID.

Line 2: We get the database key
Line 5: We use the database key (@KEYID) and the developer key (S3cr3tStuff!) to decrypt the beneficiary id

More Secure Stored Procedure

Note: While this was nice and easy for a query it is not suggested that you code this into a stored procedure as this would reveal the super secret developer key.  To avoid this, the stored procedure (SP) we will create, will pass the developer key as a parameter to the SP.

 

Database .NET from Fish, a Swiss Army Knife tool kit for Database developers

image Teradata, SQL Server, SQLite, MySQL, Oracle, Excel, Access and dBase are databases you may use on a regular basis.  If you do you probably have 3-6 Database tools to help manage them all.  Each of these has their own user interface and capabilities.  Database .NET from Fish Code Library is the one tool to help you manage all these databases and many more.   Database .NET (DBN) is a single EXE file and can be installed as a portable product on a thumb drive or a shared product on Dropbox.

Database .NET is not the best name Fish Code Library could have picked as it makes it difficult to locate it on the web without having to look at a 100 posts about Microsoft’s .NET foundation.

DBN has so many features that DBA’s and DB Developers would like.

Features

Portable
  • Can be run on Corporate computers that don’t have install rights, you folks know who I mean
  • Can be run from a USB Thumb drive
  • Can be run from Dropbox and one instance shared across multiple computers
Free & Premium
  • Free version does 80% of what many users need
  • Premium version is $30…. Really!!  
    That’s less than the cost of a monthly cup of coffee (Cheap coffee at that)
Single EXE
  • Very small footprint
  • No Windows installation required
  • No extra DLL’s that might cause conflicts on your system
  • Updates are simple, just replace the EXE file
  • Overhead is very low, open multiple copies of DBN to work between different servers and database types
  • Fast processing
.NET Data Providers
  • DBN has all the latest .NET drivers embedded in the tool
  • No ODBC Data Source Names to maintain outside the tool
  • Configuration for DB connections maintained by DBN
Teradata
  • Teradata supported with Full capabilities of .NET 15.x.x
Server support
  • 21 databases servers supported
  • Legacy support for dBase and Paradox, anyone remember those?
Export Formats
  • Comma Separated Values (CSV) very common format
  • Tab Separated Values (TSV) gotta love Unix support
  • XML data exchange with bloated tags
  • Excel (XLS) Because if it isn’t a database it’s a spreadsheet
  • Insert SQL portable between different database formats
  • HTML ok the web isn’t a fad and is probably going to be here a while
  • Java Script Object Notation (JSON) NoSQL document format
DOS Command mode
  • DBN  be called in a Windows Batch file
  • Inexpensive and casual ETL tool and data transfer tool
    (GeekMustHave will have an example of Teradata to SQLite Windows Batch file soon.)
  • Database to Excel refresh tool with Windows shortcuts
  • Basic SQL to JSON convertor for relational databases
Version Tracking
  • Version tracking on selected SQL scripts
Code Libraries
  • Simple code library for each database/project in Windows directories and files
  • SQL code can be quickly saved in Windows files in a structured directory
  • Copy all your SQL scripts for all you projects by copy and pasting a single directory
  • Install DBN on Dropbox or other Cloud services and have you DB tool and script everywhere you go.
Support
  • Most Excellent
  • Fish Code Library also has a number of other productivity tools
  • Forum available to post improvements and vote on them
  • Many of the improvement requests show up in the frequent updates
  • Have I mentioned Teradata support

To be a fair reviewer of database tools you do have to mention some areas where the tool could be improved

Improvements

Results Limiter
  • Allows for the number of rows returned in the result to be automatically limited without changing the SQL.  Good for large database queries  and data warehouses usage.
MAC support
  • DBM is a Windows tool, get over it or get a copy of VM Fusion and run it there
  • That being said, a native MAC version would be nice

Document SQL Server database with free tools, a new project?

Everything SQL Server Compact: Document your SQL Server database with free tools: SQL Server Compact Toolbox and DB>Doc.

While this web site references SQL Server compact it does create a new idea for how to manage the documentation of a SQL Server database within the actual database.  As the database moves so does the documentation.  You can edit the metadata outlined in the article with SQL Server Management Studio (SSMS) but it require some knowledge of  table or view “Extended Properties” and column “Description”.  There are some command line tools and suggestions of creating SQL scripts to maintain the metadata.

There should be a much easier way to maintain this documentation and take advantage of all of the metdata Extended Properties without having to remember what thier named.  There is a tool called Tech Writer which can generate great looking printed documentation from the Metadata in the database.

More research is anticipated in the area soon.

SQL Server and New Install SSMS on Hosted servers

I use SQL Server for a number of database projects.  We host some of our projects on BlueHost which is an excellent provider.  SQL Server Management Studio (SSMS) is our basic utility for access as a DBA to those databases.  After installing SSMS and connecting to BlueHost you might get this error when connecting to the server.  The server prinipal xxxx is noit able to access the datebase yyyy under the current security content.

Screenshot 2014-08-25 at 11.57.24 AM

 

My project is the BMPAdmin and it just so happens that the first database in this BlueHost SQL Server instance is the once named (arabworld) which I do not have access to.  I run into this everytime I do a new install of SSMS.

Solution is simple and I put here so I don’t have to go looking for it everytime it happens. 😉

The trick is to open the Object Explorer Deatils panel which is done by press (F7) or clicking on the menu bar (View) like shown below.

ObjExplF7

 

 

 

 

 

 

 

 

 

This should open up a panel that has the following look to it.

CollationHeader

You may have to scroll over to see the column labeled (Collation) that is the culprit. Right click on the heading of that column and you should see

Collation setting

 

The property (Collation) that is checked needs to be unchecked.  The restart you SSMS program and your database list should work.

 

 

 

 

 

 

Determine SQL Server version with a query

I’m working on converting the Adventure Works 2008 database from SQL Server into Teradata so there is a database to experiment with.  Sometimes there is an issue were the SQL Server MDF files for Adventure Works didn’t match the version of the SQL Server instance.   How do you quickly tell all the details of the version, compatibility and other goodies from your server?

Here is a SQL Server query to uncover this information.

This will return a result that looks like this

SNAGHTML1c97f636

image

The Internal Version Number is important, here is a short list of the Major SQL Versions

  • SQL Server Version Compatibility Level Internal Database Version Build
  • SQL Server 2012 RTM 110 706 11.0.2100.60
  • SQL Server 2008 R2 100 665 10.50.xxxx   <—- This matches
  • SQL Server 2008 100 661 10.00.xxxx
  • SQL Server 2005  SP2 90 612 9.00.xxxx
  • SQL Server 2005 90 611 9.00.xxxx
  • SQL Server 2000 80 539 8.00.xxx(x)

Access 2007 / 2010 ADO SQL Server 2008 execute Stored Procedure

I have a passion for writing Access front ends to SQL Server databases.  The same techniques can be used for other databases like Teradata and MySQL.  Much of what you can do in Access with SQL Server can be accomplished with linked tables however there are many time when running a SQL Server Stored Procedure or a Teradata Macro directly is a much better alternative.  Some advance SQL functions like OLAP and processing millions of rows are better done on the industrial servers not on Access direct.  Let the other server do the heavy lifting and just return a nice clean result to your Access application.

For SQL Server connections I highly recommend that you install the Microsoft Native CLI 10 drivers.  Download  This will provide your access with ADO and OLEDB support which in my opinion is the best way to talk direct to SQL Server.  The older DAO is no longer supported with 64 nit implementations of Access which is the majority of my customers.

One of the more difficult points is getting the SQL Server connection string correct for OLEDB and the Native CLI 10 drivers.  In my Access application I set this string once is a Global module that gets called from the AutoExec macro when the application starts up.

Here is an example of my SQL Server connect string for an Access application.  No you can’t have my server name and password.  Smile with tongue out

Here is an example of the code used to call an SP (spPopulateRuleLabel)  which just does some background updating of tables.  It has no parameters and returns no results. 

You will notice the (gsSQLOLEConnect) is used to supply the connection information to the Connection object.

The Stored Procedure (spPopulateRuleLabel) does some background processing and updates tables.  This would have been very difficult to do just using Access and Visual Basic.  The SQL Server TSQL allows for better control over database objects.

Just to give you an idea of what is being done by the SQL Server Stored Procedure here is the code.. Special thanks to Anush.