30

Recently, I upgraded LocalDB from version 13 to 14 using the SQL Server Express installer and this instruction. After the installation, I stopped the existing default instance (MSSQLLOCALDB) of version 13 and created a new one, which automatically used the v14.0.1000 server engine.

I often use LocalDB for Database Integration tests, i.e. in my xunit tests, I create a (temporary) database which is deleted when the test finishes. Since the new version, unfortunately all my tests fail because of the following error message:

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Users\kepflDBd0811493e18b46febf980ffb8029482a.mdf'

The odd thing is that the target path for the mdf file is incorrect, a backslash is missing between C:\Users\kepfl and DBd0811493e18b46febf980ffb8029482a.mdf (which is the random database name for a single test). The databases are created via the simple command CREATE DATABASE [databaseName] - nothing special here.

In SSMS, I see that the target locations for data, log, and backup are the following:

LocalDB target locations

However, when I try to update the location, I get another error message:

Error message when trying to update

How can I update the default locations so that LocalDB is able to create databases again? It's obvious that LocalDB does not correctly combine the default location directory and the database file name - is there a registry entry that I can edit? Or anything else?

Update after Doug's answer and sepupic's comment

According to this Stackoverflow question, the default location's should also be changeable via the registry. However, if I try to find the corresponding keys "DefaultData", "DefaultLog" and "BackupDirectory", I cannot find them in my registry. Did SQL Server v14 rename these registry keys, or moved these information out of the registry?

  • FYI, I also cannot update the Database default locations when running SSMS in admin mode. – feO2x Nov 21 '17 at 11:59
  • 1
    Please see the update in my answer. This bug has been fixed as of CU6, released in mid-April.. – Solomon Rutzky May 4 '18 at 17:55
17

UPDATE

As of CU 6 for SQL Server 2017, this bug has been fixed. It is now possible to execute the following successfully:

CREATE DATABASE [CreateDatabaseTest];
DROP DATABASE [CreateDatabaseTest];

The problem, and the fact that it is fixed in CU6, is documented in the following KB article:
FIX: "Access is denied" error when you try to create a database in SQL Server 2017 Express LocalDB

To get the Cumulative Update, please go to the following page and grab the top (i.e. latest) build, which might be newer than CU6 depending on when you see this:

SQL Server 2017 build versions


BELOW INFO OBSOLETE AS OF SQL SERVER 2017 CU6 (Released 2018-04-17)

The lack of a backslash in the combined Path + File name appears to be a bug with SQL Server 2017. I just ran into it myself. I even tried editing the Registry to add a DefaultData string Value for C:\Users\MyAccountName\ in both of the following Keys (the 3 default paths are not in any of the LocalDB registry keys that I looked through):

  • Computer\HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\UserInstances\{some-GUID-value}
  • Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14E.LOCALDB\MSSQLServer

And yes, I did shutdown and start up again the LocalDB instance in both attempts.

However, I am not convinced that not being able to change the default paths is a bug as it might just be poor documentation and poor error handling combined. I say this because I just tried editing the default locations for SQL Server LocalDB versions 2014, 2016, and 2017, and all resulted in the exact same error, which in itself is odd due to being from RegCreateKeyEx(), which should be dealing with the Registry and not the file system.

Not being able to change the path is unfortunate due to the lack of backslash when creating a new Database without specifying the files to use. However, I was able to create a new Database using the full CREATE DATABASE syntax as follows:

CREATE DATABASE [XXXXX]
 CONTAINMENT = NONE
 ON PRIMARY 
( NAME = N'XXXXX_sys', FILENAME = N'C:\Users\MyAccountName\XXXXX_sys.mdf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
 FILEGROUP [Tables] DEFAULT
( NAME = N'XXXXX_data', FILENAME = N'C:\Users\MyAccountName\XXXXX_data.ndf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'XXXXX_log', FILENAME = N'C:\Users\MyAccountName\XXXXX_log.ldf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 COLLATE Latin1_General_100_CS_AS_KS_WS_SC;
GO
  • We're taking the approach in our database setup that if it's a LocalDb connection, that we specify the path of the MDF file, just like in your workaround. But it does not seem necessary to also specify the name of the LDF or LOG ON section of the CREATE DATABASE statement. The LDF files seem to get created, by default, in the same location as the MDF file. (Our use case of LocalDb is mainly for use in automated tests.) – tgharold Dec 28 '17 at 20:28
  • @tgharold Please see the update at the top of my answer. This bug was very recently fixed in the new CU6 patch :-). – Solomon Rutzky May 8 '18 at 15:23
5

I ran into the same issue and found a workaround that shouldn't have any clear drawbacks (if I'm forgetting something, please correct me). It's based on Solomons answer, but without the need to specify the absolute path to the database files directly.

DECLARE @databaseName NVARCHAR(MAX) = 'MyDatabase'

DECLARE @dataFilePath NVARCHAR(MAX) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR) 
    + FORMATMESSAGE('\%s.mdf', @databaseName)

DECLARE @sql NVARCHAR(MAX) = FORMATMESSAGE(
    'CREATE DATABASE %s ON PRIMARY ( NAME = %s, FILENAME = ''%s'' )', 
    quotename(@databaseName), quotename(@databaseName), @dataFilePath
)

EXEC (@sql)

It uses dynamic sql and is not exactly pretty, but it gets the job done until there is an official fix to the issue.

  • 1
    Interesting. It might be slightly better to move the 2nd QUOTENAME to the 2nd param of FORMATMESSAGE and put double quotes around the file path: FORMATMESSAGE(N'CREATE DATABASE %s ON PRIMARY ( NAME = %s, FILENAME = "%s" )', QUOTENAME(@databaseName), QUOTENAME(@databaseName), @dataFilePath);. But it seems to work as you have it now, so +1 for this approach. There is still a case for either hard-coding the name or passing in a path: when you don't want to use the USERPROFILE root. But you could allow for that here and just default to InstanceDefaultDataPath if @Path IS NULL. :-) – Solomon Rutzky Mar 7 '18 at 22:33
  • Thank you. I've edited the answer with your suggestion. I agree hard-coding the absolute path is definitely still a valid solution. In our scenario though, we only recreate the database during automatic testing and I had to make sure it'd work for all my colleagues and build server, without having to coordinate everyone creating a folder at the exact same path on their local machine. :-) – Nikolaj Dam Larsen Mar 8 '18 at 5:56
4

I'm facing this issue too. The only workaround I found would be to grant the write access to c:\Users\ to Everyone (or something like that) and let it create the mdf files wherever it wants.

  • 1
    Thanks, this solved it for me as well! This workaround sucks, but it's only on our local build server, so I don't care about the additional user rights. – Hannes Sachsenhofer Mar 21 '18 at 15:15
  • @HannesSachsenhofer: I agree, it sucks. But I was promised by the LocalDB dev team they'll fix this bug in the upcoming hot-fix release. – abatishchev Mar 21 '18 at 19:52
  • Grant write access to everyone seems like a pretty bad workaround to me – Raphael May 8 '18 at 15:17
  • @Raphael: Why? You have multiple users on you dev box? And whom you don't trust? – abatishchev May 8 '18 at 21:08
2

Thank you for your concise explanation of this problem. I ran into this same issue yesterday. I still haven't found a permanent solution, but here is my current workaround.

I'm using the Database.EnsureCreated() function to create the database.

Set connection string to include 'AtachDBFilename=' setting.

Server=(LocalDB)\\MSSQLLocalDB;Database=ExploreCalifornia;AttachDbFilename=.\\ExploreCalifornia.mdf;Trusted_Connection=True;MultipleActiveResultSets=true

Run the application. It will generate an error:

Cannot attach the file '.\ExploreCalifornia.mdf' as database 'ExploreCalifornia'.

but it will create the database.

After that, change the connection string and remove 'AtachDBFilename='.

 Server=(localdb)\\MSSQLLocalDB;Database=ExploreCalifornia;Trusted_Connection=True;MultipleActiveResultSets=true

I ran the application again with no errors and tables were created.

Hope this helps!

  • A couple of questions: First, just to be sure - you were getting an (Access is denied) error initially, and this resolved that, correct? Second - SQL Server Management Studio is the only application mentioned by the IP, and this doesn't sound like something you did from there - what application did you do all this from? – RDFozz Nov 21 '17 at 21:30
  • Thanks for your answer, but this does not really solve my problem. My test code creates a new database with a simple CREATE DATABASE [databasename] against LocalDB and this very statement is causing problems as LocalDB wrongly concatenates the default locations directory with the randomly generated database name (see paragraph 3 and 4 of my question). I need a way to fix the default locations so that this concatenation problem does not occur. – feO2x Nov 22 '17 at 8:35
  • Currently, I'm not able to create a database via SQL / DDL at all. It doesn't matter if I run the statement via SSMS, or from code, or anywhere else, because LocalDB always tries to create the database directly in the Users directory (which is completely wrong, no files are allowed to exist in this directory). – feO2x Nov 22 '17 at 8:52
  • You've possibly misspelled AttachDBFilename. – tgharold Dec 27 '17 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.