Author: Soheil Bakhshi

Administartion, SQL Server, T-SQL

How to store a SQL Server database diagram into a file and share it with others?

Sometimes you might feel you need to store the database diagrams in a file basis. I’ve faced to this feeling specially when I was testing some databases which were under development. I didn’t like to create the whole database diagrams that I’ve created before in a database that a new version of it is newly released for testing as it is a kind of rework and it is a time consuming process. So I decided to store the database diagrams in a file system basis that I can reuse it again and again. The following solution could be great for sharing the database diagrams in the development team as well by sending them the diagram file by email and/or storing the database diagram file in a network shared folder. Here is a work around that might help you. First of all you should install diagram support in your database. To do so just right click on the database diagrams in object explorer in SQL Server Management Studio and select “Install Diagram Support”.

Continue reading “How to store a SQL Server database diagram into a file and share it with others?” →

Business Intelligence - BI, SQL Server, SQL Server Integration Services - SSIS, T-SQL

How to hash sensitive data for maximising security in SQL Server 2005 and later versions?

SENSITIVE DATA! It’s an interesting topic! In this post I’m trying to explain how to hash data to increase security during ETL. Assume that we have sensitive data stored in several secured source systems. The source systems are located in different countries and different regions. As the source systems themselves are secured, how we can cover data security needs during ETL process to read data from source systems and load into staging area? Apart from using secured network infrastructure, VPN, network tunnelling etc. we need to cover data layer security to extract sensitive data. One of the best ways is hashing data when it is extracting from source databases. Hashbytes is a T-SQL function that is available in SQL Server 2005 and later. As you might know there are many hashing algorithms, but, different SQL Server versions are supporting different range of hashing algorithms. For instance SHA1 is supported by SQL Server 2005 and later, but, if you are looking more secure hashing systems like SHA2, 256 (32 bytes) or 512 (64 bytes), you should use SQL Server 2012. Actually the hashbytes function will return null in earlier versions of SQL Server. If you are looking for a higher level of security like SHA3 that is originally known as “Keccak” you should wait for it for a long time as based on my investigations it is not supported even in SQL Server 2014 OR you can write your own SHA3 code OR just rely on some third party codes available on the Internet! So let’s get our hands dirty with using hashbytes in different versions of SQL Server.

Continue reading “How to hash sensitive data for maximising security in SQL Server 2005 and later versions?” →

SQL Server

Installing SQL Server 2014 CTP1

  • After downloading the SQL Server 2014, mount the media file downloaded and run “setup.exe”
  • In SQL Server Installation Centre click on “Installation”

image

  • Click on “New SQL Server stand-alone installation or add features to an existing installation”

image

Note that if you have any of previous versions of SQL Server installed on your machine the setup process will fail because this release of SQL Server cannot be installed in until the existing instances of SQL Servers are uninstalled.

image

If you click on “Failed” you can read the complete error message that is “Previous SQL product installation failed. A SQL product other than SQL Server 2014 CTP1 is detected. You cannot install this release until the existing instances of SQL products are uninstalled.”

image

Continue reading “Installing SQL Server 2014 CTP1” →

Administartion, SQL Server, T-SQL

How to define database owner SQL server wide

What does it actually mean that the database doesn’t have a database owner?! It seems odd, but, it is possible. In some cases that the database is created by an application it really happens that the database doesn’t have any database owner. For instance, the databases  generated by CRM Deployment Manager doesn’t have database owner. So what if we had a bunch of databases in the SQL Server that doesn’t have any database owners? Okay, let me explain. I’ve faced to a situation that we needed to create some database diagrams for many databases and all of those databases where CRM databases created by CRM Deployment Manager. Hence, when we were trying to generate a database diagram an error message were raising saying the database doesn’t have DB owner.  So it was very time consuming if we wanted to define a DB owner for each database individually using the GUI. The following code will define a particular database owner for the databases server wide. (In our situation it was a service user.)

Continue reading “How to define database owner SQL server wide” →