CODEWITHSUNDEEP
sql-serverdatabase-replication
Jed
Jed

Reputation: 1043

SQL Server read only failover database CLR enabled

We have enabled CLR on the primary database with a read only synchronized readonly failover database and it works perfectly using the following instruction.

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = 'CLR_Login')
BEGIN
    DROP USER CLR_Login;
END

CREATE USER CLR_Login FOR LOGIN CLR_Login
GO

DROP FUNCTION IF EXISTS [dbo].[EncryptData]
GO

DROP FUNCTION IF EXISTS [dbo].[DecryptData]
GO

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'CryptoClr')
   DROP ASSEMBLY CryptoClr
GO

CREATE ASSEMBLY CryptoClr FROM 'C:\CLR\CryptoClr.dll' WITH PERMISSION_SET = UNSAFE
GO

CREATE FUNCTION EncryptData (@clearData NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME CryptoClr.UserDefinedFunctions.EncryptData
GO

CREATE FUNCTION DecryptData (@encryptedData NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME CryptoClr.UserDefinedFunctions.DecryptData
GO

SELECT dbo.DecryptData('sdfasdf/asdfasd/aadsf444f4') as 'Decrypted from CLR'
select dbo.EncryptData('4435345345345')

Why went we try execute the DecryptData or EncryptData functions do we get a System.IO.FileLoadException is there something specific we need to enable on the read only database to use this feature?

Upvotes: 0

Views: 43

Answers (2)

Jed
Jed

Reputation: 1043

so we ended up setting this server as the primary then updating the owner to the same SID as the main primary then resetting the primary back to the original one. If the owners dont match you get this error. Here are the two documents.

https://www.sqlskills.com/blogs/paul/the-curious-case-of-the-clr-assembly-failure-after-an-ag-failover/

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/development/error-run-clr-object-create-assembly

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

The CryptoClr.dll assembly has been loaded into the database, and will be present on the read-only replica.

Look in the code for CryptoClr and see if it's accessing the local filesystem. It's UNSAFE so it's allowed to do that.

If you have the full stack trace it will tell you where the failure is occurring.

Upvotes: 0

Related Questions