Tag Archives: sql 2012

SQL Server Configuration Manager in SQL Server Denali

While I was working with the SQL Server Configuration Manager in SQL Server 2005 mentioned in yesterday’s post, the new features in SQL Server Denali came to my mind. Here is why I liked the Configuration Manager in SQL Server Denali.

The first striking change is that the number of tabs have increased from 3 in SQL Server 2008 to 6. This means that the options tightly stuffed inside the 3 tabs have been allocated their own space which results in ease of management.

The feature which a DBA would appreciate is the Startup Parameters tab. In the earlier versions of SQL Server, the values in the startup parameters had to be edited in-line and a small typo there would not allow the service to start as posted here. But in SQL Server Denali, a seperate text box has been provided to Add/Update the startup parameters. This makes the DBA’s job relatively easy and the chances of erroneous entry in startup parameters are less.

The other DBA friendly feature is the ability to change the directory where dump files are generated. SQL Server Dump files (*.dmp, *.mdmp) files are generated when SQL Server encounters an issue like Access Violation or when SQL Server crashes. These files contains information about what SQL Server was doing prior to the crash or error.

In earlier versions of SQL Server, any SQL Server Dump files were by default created in the directory where the SQL Server Error Log was located. I had faced this issue on a SQL Server 2000 instance where the dump files were continuously getting generated on the C drive and managing space on C drive was a challenge.

In SQL Server Denali, the SQL Server Configuration Manager includes the option to change the Dump directory (under Advanced tab).

I wish we had this feature in SQL Server 2000. My team members would agree to this, since they had a tough time managing the .mdmp files in late 2009.

Overall the new SQL Server Configuration Manager is DBA friendly. Since we are still at CTP1, hoping to have many more such good features by the time it reaches RTM.

Contained Databases in SQL Server Denali

Last Sunday I spent some time exploring the features of SQL Server Denali. One of the new features is Contained Databases. In short a Contained Database is independent of the SQL Server Instance. All the database metadata and user information is stored inside the database itself. A user defined in this database need not have a corresponding login. This feature is very useful in Non-Production environment where the databases are moved across instances. No more orphaned users or syncing of logins across instances. This MSDN article has more details on Contained Databases.

Configuring Contained Databases Property

Contained Databases feature is not enabled by default. It can be enabled through T-SQL as below.

sp_configure 'show advanced options',1
sp_configure 'contained database authentication', 1

It can also be enabled by setting the Enable Contained Databases to TRUE using SSMS GUI as in this screenshot.

Creating Contained Databases

After enabling this option, the next step is to create a Contained Database. While creating the database, changing the Containment type to Partial will make the database as Contained. By default this option is set to None.

It can also be created using the below T-SQL command.

CREATE DATABASE [ContainedExample]
( NAME = N'ContainedExample', FILENAME = N'C:ContainedExample.mdf' )
( NAME = N'ContainedExample_log', FILENAME = N'C:ContainedExample_log.ldf')

Creating Users with Password

In this database like in earlier versions of SQL Server, users can be created which are associated with logins. The new feature is that a new user with password which is not associated with a login can also be created for Contained Databases. Like I mentioned earlier, the password is also stored within the database.

The T-SQL command for creating a user with password is as below.

USE [ContainedExample]
CREATE USER [username] WITH PASSWORD=N'Strong@Password'

Connecting to SSMS

In SSMS just entering the user name and password of this user is not enough to connect to the instance. Since this user does not have an associated login, SSMS raises the following error.

Error: 18456, Severity: 14, State: 5.
Login failed for user 'containeduser'. Reason: Could not find a login matching the name provided.

In order to connect using the credentials of a user with password through SSMS, the Contained Database name needs to be entered in the Connect to Database drop down box.

“Un-Containing” the database

A Contained Database can be “un-contained” as a normal database. The database property can be changed in SSMS or using a T-SQL command as the one below.

USE [master]

If the Contained Database has an user with password, this command would fail with the below error.

Msg 33233, Level 16, State 1, Line 1
You can only create a user with a password in a contained database.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Before changing the Containment option, SQL Server tries to create login within the database (most probably creating a user for the login executing the command. I was not able to track this down), hence this command would fail. After dropping the user with password I was able to change the Containment option to None. If the database does not have an user with password, the Containment option changes without any errors.

Converting a database to a Contained Database

A database can be converted to a Contained Database using both SSMS and T-SQL.

USE [master]

If this database has users associated with Windows Logins they are contained in the database without any extra effort. In order to contain the users which are associated with SQL Server logins, sp_migrate_user_to_contained stored procedure needs to be executed. This will convert the SQL Server Logins to users with password. The following command will convert a user associated with a SQL Server Login named tlogin to a user with password.

USE ContainedDatabase
@username = N'TLOGIN',
@rename = N'keep_name',
@disablelogin = N'do_not_disable_login' ;

Once this is done, the newly contained database is ready to go.


Contained Databases is a very useful feature. This reduces a lot of work for the DBA while shipping the databases from one environment to another. But the Security Auditors will need to update their auditing procedures due to the threats mentioned here.

Hide SQL Server Instance on all versions

It is holiday season everywhere. It means that Freeze on Change Controls. Nothing changes so nothing breaks. Also the instances my team supports have been very supportive this holiday season. Hence most of my recent posts have been inspired by the questions that I respond in the forums, not from the issues that I face. This one is also a detailed version of the answer that I had posted.

While trying to connect to an instance using SQL Server Management Studio (SSMS), it *looks* for all SQL Server instances available on the network. Similarly sqlcmd -L command lists the SQL Server instances available on the network.

Due to some reasons you may want to hide these instances so that they do not get listed in SSMS or sqlcmd. Here is how to do it on various versions of SQL Server.

SQL Server 2000

  • Open Server Network Utility
  • Select the properties of TCP/IP protocol
  • Select the Hide Server option.

For this change to take effect, the SQL Server service needs to be restarted. This hides the instance alright but there is a drawback. The Default port gets changed to 2433 and this cannot be changed! Certainly not an good thing to try out on Production Instances.

SQL Server 2005 & above

Starting SQL Server 2005, the feature to “hide” an instance is available in SQL Server Configuration Manager.

Unlike SQL Server 2000, there is no drawback with this. However the service needs to be restarted after making this change. The instance can also be hidden through Registry.

The location of the key (MSSQL.1 in this example) changes according to the Instance Name.

It should be noted that *hiding* the instance will only prevent it from getting listed in the SSMS or in sqlcmd/osql. It does not prevent the users from connecting to it by directly entering the instance name.

My experiments with Maintenance Plans in SQL Server Denali

For sometime now everyday I used to think “Today I will spend some time on Denali”. Due to various reasons it would somehow get missed in my To-do list. I had downloaded and installed SQL Server Denali CTP1 as soon as it was available for download.

When it comes to comparing features and reporting bugs in SQL Server as far I have seen no one can beat Aaron Bertrand (blog | twitter). He had already posted quite a number of articles on the new features and lot of other stuff about Denali here.

Maintenance Planned!

Today I decided that I will give it a shot. I finally fired up Windows Server 2008 on my VM and started SSMS. Connected to the instance and now what? Since in the SQL Server forums many of the questions revolve around Database Backups, Maintenance Plans was the first thing that came to my mind. Maintenance Plan is one of the features in SQL Server which has been constantly improving.

Finally I was there looking at the Maintenance Plans node. Most noticeable difference was that there was no New Maintenace Plan option in the context menu.

I was left with only the Maintenance Plan Wizard to create the Maintenance Plans. There was not much change in the screens, all looked same as the one in SQL Server 2008.

Now the Maintenance Plan is created, the next step is to check how it looks like while editing and what new features are added there. Right clicked on the Maintenance Plan and click Modify.

Modify? Where is Modify? Opened my small eyes wide open and searched, no Modify! The next destination was obviously SQL Server Denali CTP1 Release Notes. Here is the Note about Maintenance Plans.

This is the reason why the New Maintenance Plan and Modify options were missing in the Maintenance Plans. The only option to Edit the Maintenance Plans is to use the Business Intelligence Development Studio (BIDS). So, opened up a new Integration Services Project in BIDS and imported the Maintenance Plan by right clicking on SSIS Packages and Add existing package.

Double clicking on the Tasks again did not open the Task Designer due to the reasons mentioned earlier.

As of now the only option to edit the Maintenance Plan taks is to make use of the Properties tab for each task as in the below screenshot.

It was indeed a journey on a bumpy road. Before I could explore any further, my phone started ringing. It was a call from the office, “Pradeep, some issues while changing the service account….“. My day had just begun. Shut the VM quickly to hop on to the call.