SSAS 2022 Connections fail following restart
I'm using an application which has SSAS 2022 OLAP cubes at the back end. We are having an issue that whenever we restart the server or the service, the connections to the SQL Server that is the data source break. I suspect this is a consequence of SSAS CU1 behaviour where the connection string is encrypted, but - because they get encrypted - there's no way to identify what the change is. SSAS is on the same instance as the SQL Server. Before a restart, i've tried adjusting a few connection properties, notably Impersonation set to Service Account Trust Server Certifcate to True Encryption for data to Optional The connection works fine with these settings. However, post reboot I get a connection error whenver I try toprocess any objects: Errors in the back-end database access module. No provider was specified for the data source. We are using MSOLEDB19 so should be fine, but it seems that post reboot the encrypted connection is somehow misconfiguring. Appreciate any guidance on what could be happening here? I can't avoid restarting the server as org policy demands servers are rebooted every fortnight.
Is multiple Certificates possible on one server?
Hey all, So let me set the scene for you. Through an audit finding, we are now looking into encrypting our SQL server backups. That in itself isn't a problem, I understand how to do that, I have been researching that and it is fairly doable in our environments. Most of our servers are in stacks based on their business need. However, we have a few servers that are a conglomeration of databases from multiple production sources. So my question is this, if the certificates from production box A, B, and C are all properly place on the server (and this also assumes a key in the master database was created), can I restore databases from A, B, and C on Server D? Please let me know if my question makes sense and if you know the answer. I have been looking and I can't find anything on this specific use case. Thanks in advance, Chap
Running SQL Agent Jobs with Always Encrypted Column
I am experimenting and exploring always encrypted feature in MSSQL server 2016 in local database. As part of the experiment, I tried to apply the feature in SQL Jobs. I did below steps, but I am getting error. I have written a stored procedure, inside that I am trying to insert an encrypted column of one table to an another encrypted column of another table. Created table(source) with the below script where the FirstName column is encrypted. CREATE TABLE [dbo].[Users]( [FirstName] [nvarchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL GO Created another table(destination) with the below script where the FirstName column is encrypted. CREATE TABLE [dbo].[AppUsers]( [FirstName] [nvarchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL ) ON [PRIMARY] GO Then I created a Stored Procedure like below where the first name is passed as a parameter to the SPROC, searched for that record in Users table and inserting the FirstName value into FirstName column of AppUsers table. CREATE PROCEDURE [dbo].[TestSproc] @name NVARCHAR (50) AS BEGIN SET NOCOUNT ON; DECLARE @testValue AS NVARCHAR (50) = (SELECT FirstName FROM Users WHERE FirstName = @name); INSERT INTO AppUsers VALUES (@testValue); SELECT * FROM AppUsers; END Now while executing the SPROC, gives expected result. Declare @firstName nvarchar(50) = 'JohnDoe' EXEC dbo.TestSproc @firstName But when it is scheduled as job as below script, produces below error. USE TestDatabase GO Declare @name nvarchar(50) = 'JohnDoe' EXEC dbo.TestSproc @name Error: Encryption scheme mismatch for columns/variables '@name'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '0' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'CPG142_LT1277') (or weaker). (Microsoft SQL Server, Error: 33299) Can someone help to arrive solution for the case?