Access denied CREATE DATABASE LocalDB

Question

I installed SSMS 18. I installed a new LocalDB instance by choosing it from the SQL Server Express 2017 installation as it is not yet included in the SSMS 18 installation.

After installation when I try creating a database with the SQL below.

CREATE DATABASE Test

I receive the following error:

Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Users\brechtTest.mdf'.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

One thing I notice, but not sure is relevant, is that the filepath does not make sense at all. There is a backslash missing.

I bring up the server properties and try and add a backslash to maybe solve the problem.

But then this error hits me.

After which I try a hail mary and give everyone permissions on Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14E.LOCALDB\MSSQLServer. That prevents the error from popping up when changing the directories in the server options. But it doesn't solve the original issue.

The weird thing is that creating a database via the GUI (like below) works without a problem. But I need CREATE DATABASE to work because I'm receiving errors with EntityFrameworkCore migrations utilizing this functionality.

As soon as I allow myself all permissions on "C:\Users" I can create databases with CREATE DATABASE, because the used filepath is "C:\Users\brechtTest.mdf". But that filepath makes no sense.

Some things I've tried:

• Reinstalling LocalDB.
• Running SSMS as administrator.
• Configure SQL server service to use the local user.
• Edit the Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14E.LOCALDB\MSSQLServer keys to include a backslash.

How can I get this to function properly, i.e. how can I adjust the default path to include a backslash so that "C:\Users\brechtTest.mdf" becomes "C:\Users\brecht\Test.mdf"?

Answer 1

This error is due to a bug in the file creation path in SQL Express 14.0.100. Follow these steps if you are affected:

1. Close SSMS and any running instances of SQL Server in Task Manager > Processes including SQL Server Telemetry and SQL Server Client NT related tasks.
2. Navigate to %LocalAppData%\Microsoft\Microsoft SQL Server Local DB\Instances\ in Windows Explorer.
3. Remove any local instances you installed like MSSQLLocalDB.

4. Open your favorite CLI (like Command Prompt) and use these sqllocaldb commands. If it says the commands cannot be executed because the instance MSSQLLocalDB does not exist, continue to the next step.

   sqllocaldb stop MSSQLLocalDB

   sqllocaldb delete MSSQLLocalDB

5. Update your locally installed SQL Server with the latest Cumulative Update https://www.microsoft.com/en-us/download/confirmation.aspx?id=56128. You can see an overview of updates in the Update Center for Microsoft SQL Server on Technet: https://technet.microsoft.com/en-us/library/ff803383.aspx.

6. After a succesful update, use the sqllocaldb create command.

   sqllocaldb create "MSSQLLocalDB"

7. Your CLI should prompt that it has created a localdb with version 14.0.300 or higher:

LocalDB instance "MSSQLLocalDB" created with version 14.0.3223.3.

If you successfully updated the local db instance but you are receiving this error after: SqlException: 'Cannot create file 'C:\Users\%USERNAME%\Database.mdf' because it already exists., please remove the file in the specified path and try again.