Login Failed for MSSQLLocalDB when running IIS Express from Jenkins

Question

I currently use Jenkins to build my solution and run some Selenium Tests against my website. At the minute this points to a hard-coded URL on a different server, however, I have changed this to load the website in an IIS Express instance on the Jenkins server allowing the Selenium tests to run against the current version of the code.

The process goes as follows:

1. Commit happens on branch, Bitbucket lets Jenkins know and then Jenkins jumps into action.
2. Solution is built
3. Selenium Tests are started
   • A Process is created in C# pointing to the local IISExpress.exe and loads website code into it
   • We have a DB Project in Visual Studio which is loaded using Dacpac into (localdb)\MSSQLLocalDB
   • The newly created DB is populated with all the required data
   • The individual tests then run
   • Once complete the database is deleted

Currently the website is loaded in IIS Express successfully, the database is created and populated, but once the Website tries to access the database it receives an access denied error.

The error recieved is: Cannot open database XXX request by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'.

I have added logging throughout the solution and have found that the account which creates the database successfully when running from Jenkins is 'NT AUTHORITY\SYSTEM', and then the account which is running the IIS Express instance (which is started from Jenkins) is also 'NT AUTHORITY\SYSTEM', so am I am not entirely sure why it can create the database and populated it, but then fails access if via IIS Express.

The connection is set to use Integrated Security, and does run fine when run locally using Visual Studio. I have attempted to not use Integrated Security and instead create a SQL User, but I receive the same error.

We use NUnit to run the selenium tests and IIS Express and the DB are created from a SetUpFixture class which runs once on setup and once on teardown.

IIS Express is run using the following code:

string programFiles = Environment.GetFolderPath(Environment.SpecialFolder.ProgramFilesX86);

_iisProcess = new Process();
_iisProcess.StartInfo.FileName = programFiles + @"\IIS Express\iisexpress.exe";
_iisProcess.StartInfo.Arguments = $"/path:\"{_applicationPath}\" /port:{_port}";

if (!_showConsoleWindow)
{
    _iisProcess.StartInfo.CreateNoWindow = true;
    _iisProcess.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
}

_iisProcess.Start();

I know this works as once the tests start I can remote onto the Jenkins box and view the website.

The local DB is being created with the following code:

string connectionString = $@"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True;";

DacServices instance = new DacServices(connectionString);
string dacpacPath = Path.GetFullPath(
                Path.Combine(
                    TestContext.CurrentContext.TestDirectory,
                    dacpacRelativePath
                ));

using (DacPackage dacpac = DacPackage.Load(dacpacPath))
{
  instance.Deploy(dacpac, _databaseName, true, 
                    new DacDeployOptions {
                        CreateNewDatabase = true,
                        IgnorePermissions = true,
                        IgnoreLoginSids = true,
                        IgnoreRoleMembership = true,
                        IgnoreUserSettingsObjects = true
                    });
}

I know this works as I can view the folder for the user NT AUTHORITY\SYSTEM and can see that the Database is there.

As IIS Express is being run by the same user that the Database is created using, I would expect it to not have an issue accessing the database. I have set Selenium to take a number of screenshots when running the tests and the 2 errors I receive when the website is attempting to access the Local DB are:

1. No process is on the other end of the pipe
2. Cannot open database XXX requested by the login. The login failed.

Generally the first few tests to run receive the first error, then every test after that receives the second.

Has anybody ever had this issue before or can provide any suggestions of what I could try to resolve it?

Answer 1

@BugFinder that has solved the issue.

Looking into the documentation for LocalDB here I found the following point:

An instance of LocalDB owned by the built-in accounts such as NT AUTHORITY\SYSTEM can have manageability issues due to windows file system redirection. Instead use a normal windows account as the owner.

Changing this to a windows account has allowed it to work as expected.

Thanks for the help!