I have an issue where we have a user who has used his own AD account to create and deploy the cube back when he was the sole DBA.
I would like to have an proxy account with a password known internally instead of his own credentials.
For this I have created a new AD account, ProcessCube
, which I have given sysadmin
rights on the underlying SQL Engine
. I have also created a role called Process cube
on the SSAS database
with read and write
rights on every cube and read
rights on data sources
.
In SSAS I have double clicked the data sources
and under Impersonation mode
I have replaced the old credentials with the credentials of the newly created AD account ProcessCube
.
I have not deployed the cube with the new credentials, but rather let the SQL SSAS command in the SQL agent do the trick, do I need to deploy the cube first?
In the SQL Agent I've made ProcessCube
the Owner of the job which is a SQL Server Analysis Server Command
.
When I try to execute the SQL agent job I get the error:
The following system error occurred: The user name or password is incorrect. Source="Microsoft SQL Server 2016 Analysis Service
I assume I need some more credentials, what more do I need? What have I missed?
The command I try to execute in the agent is as follows:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
<KeyErrorLimit>-1</KeyErrorLimit>
<KeyNotFound>IgnoreError</KeyNotFound>
<NullKeyNotAllowed>IgnoreError</NullKeyNotAllowed>
</ErrorConfiguration>
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
<Object>
<DatabaseID>DataBaseID</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Best Answer
I got this to work now.
In Management Studios I extended
Databases->theDatabase->Data source
and from there I had the optionImpersonateInfo
with the valueImpersonateAccount
.I clicked at
ImpersonateAccount
and then on...
. From there I entered the credentials of the newly created account, and voilá.