Besides moving over user data, what is the second biggest task that makes one resist moving over to a new workstation? Moving and reconfiguring the user settings in various tools and IDEs.

Whenever IT rebuilds a machine for me, one of the first things I set about doing is to customize the environment settings to my preference. Recently, I had to setup a new workstation for myself and accidentally discovered a hidden gem inside of the SQL Server Management Studio for SQL Server 2012 – The Import/Export Settings wizard.

Using the Import/Export Settings Wizard

The Import/Export settings wizard allows one to export and share the SSMS settings between computers including, but not limited to the following:

• General Settings
• Code snippet locations
• Object Explorer options
• Menu & command line customizations
• Options
• Debugging
• Environment
• Text Editor

To demonstrate the usage of this feature, I have customized some of the keyboard, text editor and object explorer settings.

• Text Editor -> Tab & Indentation options for all languages – Indentation set to ‘smart’ and set tabs to save as spaces
• Designer -> Table & DB Designers –> Set ‘Auto generate change scripts’ to ON
• Object Explorer options –> Value for select (n) rows command – changed from 1000 to 10

Exporting user settings

The series of screenshots below explain exporting of user settings to a file.

To launch the Import/Export settings window in the SSMS for SQL Server 2012, go to Tools –> Import and Export Settings

The Wizard provides 3 options: 1. Export selected environment settings 2. Import selected environment settings 3. Reset all settings

Clicking on “Next” allows the user to choose which settings should be exported. (The wizard even indicates which settings might expose intellectual property information.)

The next window allows the user to choose the path and file name to store the settings to.

Once ready, click “Finish” to export the settings to the file.

Resetting all settings

The screenshots below show how to reset user settings (used in this case to simulate a workstation movement).

Choosing to reset settings allows a user to save the current settings, just in case one doesn’t have a backup. Since I just exported the settings, we will choose to proceed without exporting them again.

Clicking “Next” allows the user to choose whether to reset the settings to either SQL Server 2008 R2 (default configuration) or VS2010. We will choose to continue with the default and click “Finish”. Once the settings are reset, we find that SSMS immediately returns to a SQL Server 2008 R2 default configuration (Object Explorer and Properties windows are open). We can also confirm that all customizations are reverted back. Sometimes, it may be required to restart SSMS for the changes to take effect.

Importing User Settings

The screenshots below show the procedure to import user settings on another workstation.

Before importing, the system allows the user to save the current configuration, if required. We will proceed without saving the configuration.

Using the “Browse” button, one can select the file exported earlier and then click “Next”

From the exported file, one can choose the settings to import while choosing not to import any settings affected intellectual property information. In this example, I have selected to import everything. Clicking on “Finish” completes the import and (after restarting SSMS in some cases) one can confirm that user settings have been imported successfully.

Concluding

Versions of SSMS prior to SQL Server 2012 did not come with the Import/Export Settings wizard. However, starting SQL Server 2012, it becomes very easy to move user customizations and settings between workstations in a matter of minutes.

I have already generated my configuration backup file via the Export wizard – have you?

Question for you:

• Do you know of any other such cool tools & utilities that come built-in with SQL Server which you had planned to explore? Do share the list with me – I will be happy to explore them for you and present a step-by-step guide for their usage

Until we meet next time,

Be courteous. Drive responsibly.

Some time ago, I wrote about the importance of thinking about Deprecated, Discontinued Features and Breaking Changes while Upgrading to SQL Server 2012 in a guest post I wrote on SQLAuthority.com (http://bit.ly/12f8pAQ).

A number of tools and utilities are shipped with (and in addition to) SQL Server. The tools are useful in various stages of the upgrade preparation process. One such utility is the SQL Server Upgrade Advisor, which comes into picture in the first step of the upgrade process – gap identification. The SQL Server Upgrade Advisor analyses a database and installed services on a given SQL Server instance for potential upgrade blocking issues and reports any areas that need user intervention.

Today, I will do a quick walk-through of the SQL Server Upgrade Advisor for SQL Server 2012:

• The SQL Server 2012 Upgrade Advisor can be found at: http://www.microsoft.com/en-us/download/details.aspx?id=29065
  
• SQL Server is part of the SQL Server 2012 Feature Pack
  
• The SQL Server 2012 Upgrade Advisor is dependent upon the Transact-SQL ScriptDom, which is also available as part of the feature pack
  
• The SQL Server 2012 Upgrade Advisor is also available on the SQL Server 2012 Installation Media

Installation

The installation of SQL Server 2012 Upgrade Advisor is simple – it’s a straight matter of clicking “Next” through the installation screens, and you should be all set.

Running the Upgrade Advisor

The SQL Server Upgrade Advisor executes a set of rule-based checks against the selected database and instance. Below is a screen-by-screen guide to running the Upgrade Advisor:

As you can see, there are two sub-utilities of the upgrade advisor: An Analysis Wizard and the other A Report Analyzer. First up, we will launch the Analysis Wizard.

In the feature selection screen, you can choose the “Detect” option to detect the installed features of the given SQL Server instance.

Click on the “Launch Report” button to launch the Report Analyzer.

As you can see, the Report Analyzer allows you to view the report for each individual instance/feature, and also provides you in detail, the reason why a particular warning or error has been reported. It also links up to it’s dedicated SQL Advisor help (from the “tell me more” link) for possible solutions.

Important Points to Note

When running the SQL Server upgrade Advisor, it is important to note the following:

• SQL Server Upgrade Advisor uses static analysis. Hence, it will not identify issues within the T-SQL code or within the application code
  
• The Upgrade Advisor can be executed infinite number of times because it does not make any change to the instance and/or database configuration
  
• The SQL Server Upgrade advisor can only be executed against SQL server versions supported by SQL Server 2012 for the upgrade. Therefore, it cannot be executed against a SQL Server 2000 instance, because that is not supported by SQL 2012 (Deprecated features-Valid compatibility levels–COMPATIBILITY_LEVEL 80 support-Msg 15048)
  
• You may be surprised to know that the Upgrade Advisor has been around since the days of SQL Server 2005!
  
• Finally, passing all checks in the SQL Server Upgrade Advisor does not mean that the database and/or application are following the recommended best practices for the deployment – please use other tools like the SQL Server Best Practices Analyzer for the same

Until we meet next time,

Be courteous. Drive responsibly.

I was recently working on a particular loaned-in SQL Server instance and remarked to a friend that the owning team did not keep their instance up-to-date on the latest and greatest of SQL Server patches and updates. Immediately, the friend enquired as to how I came in possession of this information, to which I explained the following:

Fetching the SQL Server product version

Fetching the SQL Server Product Version is quite easy, and can be done in multiple ways. Two of the most used ones are:

Using @@VERSION

This has to be perhaps the most popular method of knowing the SQL Server product version – simple and easy to use. However for me, the query returns a little too much data in the sense that it also returns the processor architecture information, installation date, OS version, etc – something which I did not set out to do.

SELECT @@VERSION

And the output on one of my test systems is:

Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (Intel X86)
    Oct 19 2012 13:43:21
    Copyright (c) Microsoft Corporation
    Enterprise Evaluation Edition on Windows NT 6.2 <X86> (Build 8400: ) (VM)

Using SERVERPROPERTY

This one is my favourite because it is precise and provides just the information that has been requested.

SELECT SERVERPROPERTY(‘ProductVersion’) AS ProductVersion

/*

ProductVersion

——————–

11.0.3000.0

*/

Staying up-to-date with the SQL Server builds released to market by Microsoft

Now that I have the Product Version # for the installed instance of SQL Server, I can look it up in the official SQL Server build lists for the builds that are released to market by Microsoft:

• KB957826 – Where to find information about the latest SQL Server builds

The KB957826 links to various other KB articles listing the builds that have been released since particular build of SQL Server was released. For example, 11.0.3000.0 is SQL 2012 SP1 whereas a CU has already been released (http://support.microsoft.com/kb/2765331).

Alternatively, if the Microsoft KB article becomes too much information to digest, one can also refer the one on SQLServerCentral (http://www.sqlservercentral.com/articles/Build+List/71065/).

Until we meet next time,

Be courteous. Drive responsibly.

Security is a very vast subject – especially when it is about securing data in your SQL Server instance. Recently, I was working on revamping logins for a couple legacy applications and noticed a very interesting behaviour of Microsoft SQL Server.

Creating a Login

If you have created SQL Server logins using T-SQL in the past, i.e. using the CREATE LOGIN statement, you would recollect that using passwords not confirming to the standard complexity requirements required the use of a clause – CHECK_POLICY. Here’s an example:

USE [master]

GO

–Attempt to create a login that does not meet policy requirements

CREATE LOGIN ComplexityTest WITH PASSWORD = ‘ComplexityTest’, DEFAULT_DATABASE = [master];

GO

Executing the above statement generates the following error:

Msg 15118, Level 16, State 1, Line 2
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

CHECK_POLICY clause

The above statement attempts to create a login using a low-complexity password which does not comply with the security policies of most organizations. While not advisable to do so, some legacy application may require the use of such passwords. It is therefore required that we use an additional clause – CHECK_POLICY = OFF when creating the login.

USE [master]

GO

–Attempting to create a login that does not meet policy requirements using the CHECK_POLICY statement

CREATE LOGIN ComplexityTest WITH PASSWORD = ‘ComplexityTest’, CHECK_POLICY = OFF, DEFAULT_DATABASE = [master];

GO

Notice that the CREATE LOGIN statement now succeeds.

Getting the password Hash

In the interest of security, most database creation and configuration scripts of these legacy applications do not have the password written on them in plain-text. The passwords are generally represented as a hashed value. To get the hash value of the password, one can use the following script:

USE [master]

GO

–Fetching the password hash value as a VARBINARY

SELECT name,

       CAST(password AS VARBINARY(32)) AS PasswordHashValue,

       sid,

       status,

       dbname

FROM sys.syslogins WHERE name = ‘ComplexityTest’;

GO

We will be using this hash value to re-create the login, but first let’s drop the existing login first:

–Cleanup

USE master

GO

DROP LOGIN ComplexityTest

GO

Creating Logins using Hashed Passwords

Now, let us re-create the same login as above, but using the hashed password this time:

–Re-create the same login with a hashed password

–NOTE: The CHECK_POLICY clause is not used

CREATE LOGIN ComplexityTest

WITH PASSWORD = 0x02009D7C372DBC2BE69433F4652733D77BCC72B7D2D7DC60C94011A933908AA7 HASHED,

DEFAULT_DATABASE = [master];

GO

Note that we used the HASHED keyword to indicate that the password supplied was not a string value, but a hashed representation of the password. Also, we did not specify the CHECK_POLICY keyword. Based on the above tests, one would expect this query to fail as well. But, it doesn’t.

Instead, the login is successfully created. If we look at the login properties using the Object Explorer in SSMS, we see that according to SQL Server password policy has been enforced (which is clearly not the case because the password used is fairly simple and fails the complexity checks. Attempting to login using this login and the simple password also succeeds.

Conclusion – This is not a bug!

The big question at the end of this experiment is whether this behaviour is a bug with SQL Server or not? In my humble opinion, it is not a bug.

Any system should not have the ability to decrypt hashed passwords – even if the hash is generated by itself. When the encrypted hash is used, the system should proceed with the assumption that all has been taken care of as expected and is in-line with the organization’s security policies. In this case, that is exactly what is happening. SQL Server does not decrypt the hash to derive the password in plain-text and validate whether it complies with the defined security standards or not. This is also the reason why when creating passwords using the HASHED keyword, SQL Server requires that one does not define the CHECK_POLICY value explicitly.

The password hash is not reverse engineered by SQL Server because it is a one-way hash. SQL Server cannot reverse the hash to determine whether the hashed password is complex or not.

Reference:

• CREATE LOGIN: http://msdn.microsoft.com/en-us/library/ms189751.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is based on a small finding that we made when working with moving a legacy database over to SQL Server 2012. All was going good untill the configuration started. As we were moving the SQL logins from one SQL Server instance to another, creation of the logins failed with a weird error.

The legacy application comes with a script that creates the required logins using hashed password values, similar to the script below:

USE master;

GO

CREATE LOGIN SQL07

WITH PASSWORD = 0x2131214A212F26285628293328374839 HASHED,

DEFAULT_DATABASE = master;

GO

When we ran this script against SQL Server 2012, it raised the following error:

Msg 15021, Level 16, State 2, Line 1
Invalid value given for parameter PASSWORD. Specify a valid parameter value.

Root Cause

The reason behind the error is that SQL Server 2012 no longer supports passwords that use 16-byte hash values. 16-byte hash values were used when passwords were created in SQL Server 7.0 and below (gives you an idea of how old the legacy systems currently in production are!). According to Books-On-Line: “The HASHED option cannot be used with hashes created by SQL Server 7 or earlier.”

Resolution

The resolution to this issue can be one of the following:

1. Option A: Work with the vendor to get an update to the product (recommended), or at least a new password hash




• “Vendor” here refers to the 3rd party team which owns and maintains the product
  
• “New” password hash can be created by recreating the login using the plain-text password in a newer version of SQL Server


2. Option B: If you are aware of the password, you can create a new login using the plain-text password with the CHECK_POLICY clause set to OFF

Reference:

• CREATE LOGIN: http://msdn.microsoft.com/en-us/library/ms189751.aspx

Other posts on SQL Server 2012 Deprecated & Discontinued features:

Here is a recap of my posts on SQL Server 2012 deprecated and discontinued features:

• Backup & Restore database/log with password–Msg: 3032
  
• Valid compatibility levels–COMPATIBILITY_LEVEL 80 support-Msg 15048
  
• DATABASEPROPERTY replaced by DATABASEPROPERTYEX
  
• Returning result sets from triggers
  
• 32-bit systems – AWE (Address Windowing Extensions) no longer supported
  
• CREATE TRIGGER-WITH APPEND option
  
• Modify database options with sp_dboption–Msg 2812
  
• Table Hint: FASTFIRSTROW-Msg: 321
  
• sp_addserver-Remote server registration-Msg: 15663
  
• Use of OUTER operators – *= and =*; Msg: 4147
  
• Column Alias defined by a string enclosed in quotation marks
  
• sp_describe_first_result_set – replacement to FMTONLY

Until we meet next time,

Be courteous. Drive responsibly.

[EDIT: December 24, 2012; 1650hrs IST: Corrected encryption type for SQL 7 logins from 16-bit to 16-byte, described terms “vendor” and “password hash”]