Efcore: Error creating filegroup with SQL Server 2017

[DenisKolpakov]

Please, review this code part:

SqlServerMigrationsSqlGenerator.cs#L782

When I set .ForSqlServerIsMemoryOptimized() and then try to apply migration onto my db (dotnet ef database update) I receive error message

System.Data.SqlClient.SqlException (0x80131904): CREATE FILE encountered operating system error 5(Access denied.) while attempting to open or create the physical file 'C:\Users\MeDb_MOD'.

with SQL script to create MOD filegroup and db file. I add this below the DECLARE @new_path:

PRINT @path
PRINT @filename
PRINT @new_path

and got next:

C:\Users\Me\Db.mdf
Db_MOD
C:\Users\MeDb_MOD

There is no slash between username and MOD db file (folder).

[smitpatel]

Duplicate of https://github.com/aspnet/EntityFramework6/issues/384

[ajcvickers]

Setting this for @divega to investigate, since it looks like the same issue that is already being investigated for EF6.

[divega]

This is an issue with the 2017 version of LocalDB and is not related to memory optimized tables. Here are the repro steps:

1. Install SQL Server 2017 LocalDB edition:
   a. Download installer
   b. Choose Custom option
   c. Execute installer and choose Installation | Upgrade from previous version of SQL Server
   d. Upgrade LocalDB to 2017 (Visual Studio comes with 2017 SP1 included)
2. Delete MSSQLLocalDB instance from the command prompt so that a new one can be created with the
   a. sqllocaldb stop mssqllocaldb
   b. sqllocaldb delete mssqllocadlb
   c. sqllocaldb start
   d. sqllocaldb info mssqllocaldb
3. Verify that output from last command shows that MSSqlLocalDB is now version 14.x
4. Execute the following program using the Microsoft.EntityFrameworkCore.SqlServer package:
   ``` C#
   using Microsoft.EntityFrameworkCore;

namespace LocalDbBug
{
class Program
{
static void Main(string[] args)
{
using (var context = new MyContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated(); // fails
}
}
}

public class MyContext : DbContext
{
    public DbSet<Person> People { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(
            @"server=(localdb)\mssqllocaldb;database=localdbpathbug;integrated security=yes;connectretrycount=0");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>();

    }
}

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
}

}

## Workaround

You can specify the full path for the file explicitly in the connection string, e.g.:

``` c#
optionsBuilder.UseSqlServer(
@"server=(localdb)\mssqllocaldb;database=localdbpathbug;integrated security=yes;connectretrycount=0;AttachDBFileName=c:\users\name\localdbpathbug.mdf");

Cause

1. The result for SELECT SERVERPROPERTY('InstanceDefaultDataPath') has changed in a breaking way:
   a. Preview versions returned c:\Users\username\
   b. SQL Server 2017 returns c:\Users\username
2. As implied in the original post, the code at https://github.com/aspnet/EntityFrameworkCore/blob/dev/src/EFCore.SqlServer/Migrations/SqlServerMigrationsSqlGenerator.cs#L788 is not resilient to the path not containing a trailing \. It performs simple concatenation instead of using an algorithm like Path.Combine(), which in SQL would be a bit more complicated to implement.

Clearing up milestone so that we can discuss a plan in triage.

[divega]

I was wrong about out code path for "memory optimized" not being involved, however the repro I found yesterday shows that there are multiple ways to hit the same fundamental problem.

Today, I confirmed that you don't even need EF to repro this issue, and we have reported this to the SQL Server team. The saving grace of this is that Visual Studio 2017 still installs the SQL Server 2016 version of LocalDB.

Given all this, at this point we don't plan to make any code changes in EF Core or EF6 to try to compensate for the SQL Server bug.

Here are repro steps without EF:

1. Install SQL Server 2017 LocalDB
2. Open Microsoft SQL Server Management Studio
3. Connect it to any SQL Server 2017 LocalDB
4. Open a new query window
5. Execute “CREATE DATABASE Hello”

Expected result:

The database Hello is created.

Actual result:

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:UsersMyUsernameHello.mdf'.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

[ErikEJ]

This is fixed in the latest 2017 CU (CU6) https://support.microsoft.com/en-us/help/4096875/fix-access-is-denied-error-when-you-try-to-create-a-database-in-sql-se

[ajcvickers]

@dougbu See Erik's comment above. Although it looks like they are now up to CU16, so I'm guessing it would be best to go with the latest and pull in other bugs fixes as well.