SSIS Talk

When installing the Microsoft Assessment and Planning (MAP) Toolkit (commonly used for licensing renewal discussions and migration planning), one of the options is to have the tool use the new SQL Server Express LocalDB database container. Because this isn’t a normal SQL service, you may find it a bit challenging to connect to this database outside of the MAP toolkit.

If you want to connect to this database instance with SQL Server Management Studio (SSMS), use the following as your connection string: (LocalDB)\MAPToolkit. As long as you are the owner of the LocalDB instance, you will be able to connect just fine. If you are not the owner of the instance, you will get the following error:

If you run into this, verify who the owner of the LocalDB instance is, by excecuting SqlLocalDb.exe from a command line as follows:

SqlLocalDB.exe info MAPToolkit

Which should return details about who owns the instance, when it was last started, and its state (running, for instance):

LocalDB instance "MAPToolkit" started.

C:\Users\Phil.Brammer>SqlLocalDb.exe info MapToolkit
Name:               MAPToolkit
Version:            11.0.2100.60
Shared name:        
Owner:              MyDomain\Phil.Brammer
Auto-create:        No
State:              Running
Last start time:    4/23/2013 9:04:31 AM
Instance pipe name: np:\\.\pipe\LOCALDB#SH2C50D1\tsql\query

Here we can see that “MyDomain\Phil.Brammer” owns this instance. Any other user will not be able to connect to it. In order to allow other users to connect, follow these steps:

• Connect to the instance in SSMS (logged in as the owner shown above)
• For the users/groups you want to be able to connect, add them with the SQL “sysadmin” server role.
• Share the LocalDB instance. The following command will do the trick: SqlLocalDb.exe share MAPToolkit MAPToolkitShare
• Stop the MAPToolkit LocalDB instance if it is running. SqlLocalDb.exe stop MAPToolkit
• Start the MAPToolkit LocalDB instance. SqlLocalDb.exe start MAPToolkit
• As the other user, open up SSMS and connect to the shared LocalDB instance using the following connection string: (localdb)\.\MAPToolkitShare

To see details about the shared LocalDB instance, you can execute SqlLocalDb.exe info MAPToolkit -or- SqlLocalDb.exe info .\MAPToolkitShare

Either command will show the same results:

C:\Users\Phil.Brammer>SqlLocalDb.exe info .\MapToolkitShare
Name:               MAPToolkit
Version:            11.0.2100.60
Shared name:        MAPToolkitShare
Owner:              MyDomain\Phil.Brammer
Auto-create:        No
State:              Running
Last start time:    4/23/2013 9:17:35 AM
Instance pipe name: np:\\.\pipe\LOCALDB#SH80EAD1\tsql\query

You can unshare the database with SqlLocalDb.exe unshare .\MapToolkitShare

Note: LocalDB does not allow remote connections. You will need to connect from the machine where the LocalDB instance is running.