0

Recently, I managed to setup the impersonation between my GUI and the API with ASP.NET (see here). The next issue was not far away: The database access. Whenever I call an action in the API with another user (e.g. user1 who is set up as an administrator) than the one who started the application, I get the following error:

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details.)

With this stack trace:

bei System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
bei System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
bei System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
bei System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
bei System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
bei System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
bei System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
bei System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
bei System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
bei System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
bei System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
bei System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
bei System.Data.SqlClient.SqlConnection.Open()
bei Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open()
bei Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists)
bei Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseCreator.Exists()
bei Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
bei Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
bei Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
bei ReconAPI.Models.ReconDbContext..ctor() in D:\projects\recon\Trunk\ReconAPI\Models\ReconDbContext.cs:Zeile 17.
bei ReconAPI.Services.BaseService..ctor() in D:\projects\recon\Trunk\ReconAPI\Services\BaseService.cs:Zeile 11.
bei ReconAPI.Services.UserService..ctor() in :Zeile 0.
bei ReconAPI.Controllers.BaseController.get_UserService() in D:\projects\recon\Trunk\ReconAPI\Controllers\BaseController.cs:Zeile 166.
bei ReconAPI.Controllers.UsersController.Read() in D:\projects\recon\Trunk\ReconAPI\Controllers\UsersController.cs:Zeile 21.

To be very clear: Everything is working perfectly if I try it with "my" user.

My connection string is 

Server=(localdb)\mssqllocaldb;Database=Recon;AttachDbFilename=D:\projects\recon\Trunk\db\Recon.mdf;Integrated Security=SSPI

There are some people who have encountered this error before me. But either the suggested solutions do not apply for me (e.g. firewall) or do not work for me (e.g. sharing an instance).

  • I've tried to connect to this instance with user1 in the MS SQL Server Management Studio. But I all I can see are the system tables.
  • I've checked the access privileges on the database files itself: user1 has all privileges.

So I am stuck. What needs to be done to allow multiple users to access the database via the GUI/API from an impersonated IIS (Express)?

As stated here, this is simply not possible with LocalDB but should be with SQL Express. Maybe that's the way to go?

Best regards, Carsten

Community
  • 1
  • 1
Carsten Franke
  • 1,649
  • 2
  • 13
  • 30
  • `... action in the API with another user...` <= what do you mean by this? In your connection string its SSPI (integrated security). I do not see any "user" account info in the connection string so I am confused as to what exactly you mean by this. – Igor Sep 29 '16 at 14:00
  • This applies for any action/URI in the (REST-)API which accesses the database. I am developing with my user `Carsten Franke`. As mentioned, everything's fine. If I switch to user `user1` on the very same machine, I get those errors. Authentication is done with Windows authentication, as described in the linked post. – Carsten Franke Sep 29 '16 at 14:04
  • ok, got it. `I've checked the access privileges on the database files itself: user1 has all privileges.` <= define all privileges. What have you actually set, where, and how. – Igor Sep 29 '16 at 14:06
  • That's the problem if you are using a German Windows at work. It's called "Full control" and is set on the properties of the database files. And all it's parent directories. Even the tab "Effective permissions" says that `user1` has "Full control". – Carsten Franke Sep 29 '16 at 18:03
  • I gave it a try with a shared instance and a fixed user. The connection string is `Server=(localdb)\.\sharedsql;Database=Recon;Integrated Security=False;User Id=xy;Password=xy` But I still get the same error message! The only progress with this attempt is that I can access the database in MS SQL Server Management Studio when logged in as `user1`. – Carsten Franke Oct 04 '16 at 09:12

0 Answers0