Skip to main content
Delphix

Correcting Orphaned SQL Server Database Users (KBA1111)

 

 

KBA

KBA#1111

How 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.

clipboard_e25aa51069de13a0f4eda1a458c933823.png

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".

clipboard_ea6c0b03e20b26e5801d8ab47de419e4f.png

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:

  1. Reattach the Database User to the new Login after VDB Provisioning
  2. 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;

 

Note

Note:

Logins using Active Directory (Kerberos/NTLM) authentication are not susceptible to this issue, and are ideal to ensure portability of logins between different SQL Server instances in the same domain.