I have A clustered SQL Server Instance that does not fail-over completely due to using SQL authenticating credentials on an appvolumes database for a VDI database.

Question

I have A clustered SQL Server Instance that does not fail-over completely due to using SQL authenticating credentials on an appvolumes database for a VDI database.

Marc o 0

Hello, I have A clustered SQL Server Instance that does not fail over completely due to using SQL authenticating credentials on an AppVolumes database for a VDI database. The account being used to access the database has the user mapping to the appvolumes database removed when it's in the secondary role, so that when it does fail over, the account can not access the database. Is there some reason Appvolumes or VDI cannot use Windows authenticated accounts? If so, how do I make the SQL authenticated work on both servers and fail over properly without requiring human intervention?

Erland Sommarskog 127.4K Reputation points MVP Volunteer Moderator

2025-09-22T21:05:21.9333333+00:00

I am afraid that I not at all familiar AppVolumes or VDI and their databases. Are they components in Windows Server?
Marc o 0 Reputation points

2025-09-24T05:00:57.3333333+00:00

VDI - Virtual Desktop Infrastructure, hosts entire desktop environments on centralized servers.

The resource we are using is an Omnissa Horizon product.
Marc o 0 Reputation points

2025-09-24T05:08:18.42+00:00

I guess the key takeaway is we are using mixed authentication mode with SQL authenticated accounts on a few databases, and these are the ones that fail to complete the failover due to access issues on the secondary node

1 answer

Your answer

Erland Sommarskog 127.4K Reputation points MVP Volunteer Moderator

2025-09-22T21:05:21.9333333+00:00

I am afraid that I not at all familiar AppVolumes or VDI and their databases. Are they components in Windows Server?
Marc o 0 Reputation points

2025-09-24T05:00:57.3333333+00:00

VDI - Virtual Desktop Infrastructure, hosts entire desktop environments on centralized servers.

The resource we are using is an Omnissa Horizon product.
Marc o 0 Reputation points

2025-09-24T05:08:18.42+00:00

I guess the key takeaway is we are using mixed authentication mode with SQL authenticated accounts on a few databases, and these are the ones that fail to complete the failover due to access issues on the secondary node

Answer 1

Erland Sommarskog 127.4K MVP Volunteer Moderator

So if the VDI and AppVolumes databases are third-party products, you will need to ask the vendor why they want SQL authentication.

It could be that they are using an application login, and it is not uncommon that this is an SQL login. If they have a middle tier that runs as a service, they could use Windows authentication if you create logins and users for their service accounts. Provided that the vendor permits you to specify this in config file or similar.

Then again, fixing this failover problem is not difficult. You talk about a cluster, but I assume that you have an availability group, since you get this problem with the login mapping.

On the primary run

SELECT sid FROM sys.server_principals WHERE name = 'applogin'

and make note of the value

On the secondary run:

SELECT @hash = loginproperty('applogin', 'PasswordHash')
DROP LOGIN applogin
CREATE LOGIN applogin WITH PASSWORD @hash HASHED, SID = @sidfromprimary

I don't think you can use variables with CREATE LOGIN, but just paste in the values.

Karpurapu Durga Prasanna (Quadrant Resource LLC) 5 Reputation points Microsoft External Staff

2025-10-21T06:40:07.7466667+00:00

Hi @Marc o

Thanks for reaching out to the Microsoft Q&A forum.

I would also take a moment to thank  @Erland Sommarskog , for actively participating in the Q&A forum and for the solutions you’ve been sharing in the forum. Your contributions make a real difference.

I hope the details shared help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

Best Regards.
Karpurapu Durga Prasanna (Quadrant Resource LLC) 5 Reputation points Microsoft External Staff

2025-10-25T10:58:58.78+00:00

Hi @Marc o

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

Share via

I have A clustered SQL Server Instance that does not fail-over completely due to using SQL authenticating credentials on an appvolumes database for a VDI database.

1 answer

Your answer