1

I would like to use a .MDF SQL Server database file on which my unit tests are performed during a Visual Studio Test when deploying via a DevOps pipeline.

I added the .MDF and .LDF files to the unit test project and I am able to execute locally. I have confirmed that the files are properly deployed to the pipeline using a PowerShell script that lists the folder contents. I have also confirmed case-sensitivity by ensuring everything is upper-case.

When adding a Visual Studio Test step to an existing working pipeline, I initially received this error when attempting to save to the database during test initialization:

Microsoft.Data.SqlClient.SqlException: Failed to update database "D:\A\1\S\APPCORETESTS\BIN\RELEASE\NETCOREAPP3.1\MYTESTDATABASE.MDF" because the database is read-only

I added the following call during test initialize:

ALTER DATABASE [MYTESTDATABASE.MDF] SET READ_WRITE

and am now getting the following error:

Unable to open the physical file "D:\a\1\s\AppCoreTests\bin\Release\netcoreapp3.1\MYTESTDATABASE.mdf". Operating system error 5: "5(Access is denied.)"

Unable to open the physical file "D:\a\1\s\AppCoreTests\bin\Release\netcoreapp3.1\myTestDatabase_log.ldf". Operating system error 5: "5(Access is denied.)".

I am able to open a connection to the .MDF file using the following connection string (from a config, hence the two backslash "\"):

"Server=(LocalDB)\\MSSQLLocalDB;AttachDbFileName='|DataDirectory|\\MYTESTDATABASE.MDF'"

and I am able to run a query that returns several databases including mine (as well as tempdb, master, etc):

select name from sys.databases

If this error was occurring locally, I would set the folder permissions for the folder in which the .MDF / .LDF reside, but do not know if that is possible in a DevOps pipeline or if it is the correct approach to solving this problem.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ralph Hinkley
  • 370
  • 2
  • 9

1 Answers1

0

Which agent are you using, hosted agent or self hosted agent?

Azure DevOps Pipeline accesses file via service account, we should check the service account permission and ensure it has enough permission.

If you are using hosted agent, it access the file via this account test Build Service(Org name), we need open project settings->Repositories->select the repo and check the service account test Build Service ({Org name}) permission, and also check the Pipeline->Settings

If you are using self-hosted agent and access local file, you should check the file permission. Steps: Select the local->Properties->Security, the service account name should be Administrators(Agent service name\Administrators) or Users(Agent service name\Users).

By the way, we can change the agent service account to your owner account.

Steps: Open service on the agent machine and search the agent service account, check the pic below, just change the account name and password to yours, then It will use this account to perform the operation.

enter image description here

Vito Liu
  • 7,525
  • 1
  • 8
  • 17
  • I am using a hosted agent. I can see the "test Build Service ({Org name})" user via Settings->[repo]->Permissions, but dont see any permission specific to what I would need: |Bypass policies when completing pull requests|Bypass policies when pushing|Contribute|Contribute to pull requests|Create branch|Create tag|Delete repository|Edit policies|Force push|Manage notes|Manage permissions|Read|Remove others' locks|Rename repository – Ralph Hinkley Nov 10 '20 at 13:27