Correcting Orphaned SQL Server Database Users (KBA1111)
KBA
KBA#1111How to correct orphaned SQL Server database users
After provisioning a SQL Server virtual database (VDB), you may be unable to connect to the VDB with a login that uses SQL Server Authentication.
When this occurs, you may receiving the following error when connecting to the instance:
Error: 18456, Severity: 14, State: 38. Login failed for user 'SQLUserA'. Reason: Failed to open the explicitly specified database 'MyVDBName'. [CLIENT: 192.168.0.1]
Alternatively, you may receive the following error when attempting to switch to ("USE") the database from an existing connection:
Msg 916, Level 14, State 1, Line 1 The server principal "SQLUserA" is not able to access the database "MyVDBName" under the current security context.
When attempting to troubleshoot this issue, a Database User with the same name may be visible in the provisioned VDB.
However, when viewing the user's properties in SQL Server Management Studio, the Database User will appear as the User Type "SQL user without login".
Note that the name of the Database Login may not match the name of the Database User to which it is linked. While a 1:1 mapping is most common, it may be necessary to check the Database User configuration on the source system to confirm the correct User to Login mapping.
Troubleshooting
This issue is caused by a mismatch in the Service Identifier (SID) between the Database Login in the dSource and VDB database servers - in this example, the login "SQLUserA".
By default, any new Database Login is given a random Service Identifier.
The query:
CREATE LOGIN [SQLUserA] WITH PASSWORD=N'password';
Will result in different SID values in the sys.server_principals when run on two different instances:
select name, sid, type_desc from sys.server_principals where name = 'SQLUserA';
Server 1 Output: name sid type_desc --------------------------------------------------------- SQLUserA 0x32B43DCA7A16BC4DB2421E6BDC5B46B5 SQL_LOGIN Server 2 Output: name sid type_desc --------------------------------------------------------- SQLUserA 0x2C91C5AEE1FE884694A96764FEBFD8AB SQL_LOGIN
When provisioning the database "MyVDBName" to Server 2 from a dSource or VDB from Server 1, the Database User (which exists within the database) is still mapped to the original Database Login from Server 1:
use [MyVDBName] go select name, type_desc, sid from sys.database_principals where name = 'SQLUserA';
name type_desc sid ---------------------------------------------------------- SQLUserA SQL_USER 0x32B43DCA7A16BC4DB2421E6BDC5B46B5
This results in the user appearing as "without login" from the GUI, and may result in the error messages shown above when using the SQLUserA database login.
This is deliberate behaviour of SQL Server, and is designed to prevent privileges inadvertently being granted when restoring a database between servers. This behaviour is not expected to change in SQL Server or in the Delphix Engine.
Resolution
Two options to resolve this issue:
- Reattach the Database User to the new Login after VDB Provisioning
- Re-create the Login on the Target Environment to match the Source
Reattach the Database User to the new Login after VDB Provisioning
To "reattach" the SQLUserA Database User to the SQLUserA Database Login on the current instance, you can issue the following database query:
USE [MyVDBName] GO ALTER USER SQLUserA WITH LOGIN = SQLUserA;
This will only last for the duration of the current VDB incarnation, and need to be performed each time the database is provisioned or refreshed.
It can be introduced to a post-provisioning script if required.
Re-create the Login on the Target Environment to match the Source
To prevent this issue occurring during future Provisioning or Refresh operations, you can re-create the Database Login so that it matches the Service Identifier (SID) of the Source database.
To do this, you will need to know the SID of the Database Login from the Source server, which you can extract from sys.server_principals on your Source Environment as described above.
Dropping a Database Login which is already being used for existing databases will cause problems with those database until they are also reattached as shown above. Please confirm that the Database Login on the target is not in use before making any changes.
On the target (VDB) environment:
DROP LOGIN SQLUserA; CREATE LOGIN SQLUserA WITH PASSWORD = N'YourPasswordHere', sid = 0x32B43DCA7A16BC4DB2421E6BDC5B46B5, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;