June 7, 2018 at 9:04 am
I am admin on that windows server but have lost access to the SQL Server Analysis Services Tabular server.
Is there any way to recover that?
June 7, 2018 at 11:52 am
Eric Mamet - Thursday, June 7, 2018 9:04 AMI am admin on that windows server but have lost access to the SQL Server Analysis Services Tabular server.Is there any way to recover that?
The first thing I would try is on that server, try right clicking on SSMS and run as administrator. That should let you in where you can connect and add yourself back to SSAS server administrator.
Sue
June 8, 2018 at 1:58 am
Sue_H - Thursday, June 7, 2018 11:52 AMEric Mamet - Thursday, June 7, 2018 9:04 AMI am admin on that windows server but have lost access to the SQL Server Analysis Services Tabular server.Is there any way to recover that?
The first thing I would try is on that server, try right clicking on SSMS and run as administrator. That should let you in where you can connect and add yourself back to SSAS server administrator.
Sue
Done it but without success. I still had no admin access...
June 8, 2018 at 4:08 am
Eric, did you run SSMS as administrator ON the server itself? or on your workstation?
June 8, 2018 at 4:24 am
>> Eric, did you run SSMS as administrator ON the server itself? or on your workstation?
On the server itself
June 8, 2018 at 8:22 am
Eric Mamet - Friday, June 8, 2018 1:58 AMSue_H - Thursday, June 7, 2018 11:52 AMEric Mamet - Thursday, June 7, 2018 9:04 AMI am admin on that windows server but have lost access to the SQL Server Analysis Services Tabular server.Is there any way to recover that?
The first thing I would try is on that server, try right clicking on SSMS and run as administrator. That should let you in where you can connect and add yourself back to SSAS server administrator.
Sue
Done it but without success. I still had no admin access...
You can still get in but how did you lose access - what was changed or removed that resulted in losing access. And what else have you tried?
Sue
June 8, 2018 at 8:42 am
Sue_H - Friday, June 8, 2018 8:22 AMEric Mamet - Friday, June 8, 2018 1:58 AMSue_H - Thursday, June 7, 2018 11:52 AMEric Mamet - Thursday, June 7, 2018 9:04 AMI am admin on that windows server but have lost access to the SQL Server Analysis Services Tabular server.Is there any way to recover that?
The first thing I would try is on that server, try right clicking on SSMS and run as administrator. That should let you in where you can connect and add yourself back to SSAS server administrator.
Sue
Done it but without success. I still had no admin access...
You can still get in but how did you lose access - what was changed or removed that resulted in losing access. And what else have you tried?
Sue
Someone was "reviewing" security at the time... and seems to have done something unfortunate!
But I don't know exactly what and it might be difficult to find out.
However, it is not a production database so I am wondering whether we could
- shutdown the tabular instance
- copy the database files somewhere
- re-install that instance
- reload these files...
Is that feasible without backup?
June 8, 2018 at 9:07 am
Eric Mamet - Friday, June 8, 2018 8:42 AMSue_H - Friday, June 8, 2018 8:22 AMEric Mamet - Friday, June 8, 2018 1:58 AMSue_H - Thursday, June 7, 2018 11:52 AMEric Mamet - Thursday, June 7, 2018 9:04 AMI am admin on that windows server but have lost access to the SQL Server Analysis Services Tabular server.Is there any way to recover that?
The first thing I would try is on that server, try right clicking on SSMS and run as administrator. That should let you in where you can connect and add yourself back to SSAS server administrator.
Sue
Done it but without success. I still had no admin access...
You can still get in but how did you lose access - what was changed or removed that resulted in losing access. And what else have you tried?
Sue
Someone was "reviewing" security at the time... and seems to have done something unfortunate!
But I don't know exactly what and it might be difficult to find out.However, it is not a production database so I am wondering whether we could
- shutdown the tabular instance
- copy the database files somewhere
- re-install that instance
- reload these files...
Is that feasible without backup?
I wouldn't bother. Nice security review though huh?
If they removed the server administrators and also changed one setting for SSAS - BuiltinAdminsAreServerAdmins - it could do that. I just don't know which one they did.
If they changed the BuiltinAdminsAreServerAdmins, you can set it back. Check your SSRS \OLAP\Config directory. Open the ini file for SSAS - msmdsrv.ini. In the <Security> section there are different settings related to security. They likely changed these if you positive you are a local admin on that server. This is the beginning of that section and what the defaults should be for the security settings. At the very least they changed the BuiltinAdminsAreServerAdmins to false/0 but you would want to check the others as well. <DataProtection>
<RequiredProtectionLevel>1</RequiredProtectionLevel>
</DataProtection>
<AdministrativeDataProtection>
<RequiredProtectionLevel>1</RequiredProtectionLevel>
</AdministrativeDataProtection>
<RequireClientAuthentication>1</RequireClientAuthentication>
<SecurityPackageList />
<DisableClientImpersonation>0</DisableClientImpersonation>
<BuiltinAdminsAreServerAdmins>1</BuiltinAdminsAreServerAdmins>
<ServiceAccountIsServerAdmin>1</ServiceAccountIsServerAdmin>
Change those values back to the defaults, restart SSAS and then try the right click on SSMS, Run as Administrator, connect to SSAS, add yourself, etc.
That usually works. If not, with those changes to the ini file in place, using SQL Server Configuration Manager to change the service account for SSAS to your account and go through adding your login to the administrators again, change the service account back, etc
Sue
June 9, 2018 at 2:01 am
Sue_H - Friday, June 8, 2018 9:07 AMEric Mamet - Friday, June 8, 2018 8:42 AMSue_H - Friday, June 8, 2018 8:22 AMEric Mamet - Friday, June 8, 2018 1:58 AMSue_H - Thursday, June 7, 2018 11:52 AMEric Mamet - Thursday, June 7, 2018 9:04 AMI am admin on that windows server but have lost access to the SQL Server Analysis Services Tabular server.Is there any way to recover that?
The first thing I would try is on that server, try right clicking on SSMS and run as administrator. That should let you in where you can connect and add yourself back to SSAS server administrator.
Sue
Done it but without success. I still had no admin access...
You can still get in but how did you lose access - what was changed or removed that resulted in losing access. And what else have you tried?
Sue
Someone was "reviewing" security at the time... and seems to have done something unfortunate!
But I don't know exactly what and it might be difficult to find out.However, it is not a production database so I am wondering whether we could
- shutdown the tabular instance
- copy the database files somewhere
- re-install that instance
- reload these files...
Is that feasible without backup?I wouldn't bother. Nice security review though huh?
If they removed the server administrators and also changed one setting for SSAS - BuiltinAdminsAreServerAdmins - it could do that. I just don't know which one they did.
If they changed the BuiltinAdminsAreServerAdmins, you can set it back. Check your SSRS \OLAP\Config directory. Open the ini file for SSAS - msmdsrv.ini. In the <Security> section there are different settings related to security. They likely changed these if you positive you are a local admin on that server. This is the beginning of that section and what the defaults should be for the security settings. At the very least they changed the BuiltinAdminsAreServerAdmins to false/0 but you would want to check the others as well.<DataProtection>
<RequiredProtectionLevel>1</RequiredProtectionLevel>
</DataProtection>
<AdministrativeDataProtection>
<RequiredProtectionLevel>1</RequiredProtectionLevel>
</AdministrativeDataProtection>
<RequireClientAuthentication>1</RequireClientAuthentication>
<SecurityPackageList />
<DisableClientImpersonation>0</DisableClientImpersonation>
<BuiltinAdminsAreServerAdmins>1</BuiltinAdminsAreServerAdmins>
<ServiceAccountIsServerAdmin>1</ServiceAccountIsServerAdmin>Change those values back to the defaults, restart SSAS and then try the right click on SSMS, Run as Administrator, connect to SSAS, add yourself, etc.
That usually works. If not, with those changes to the ini file in place, using SQL Server Configuration Manager to change the service account for SSAS to your account and go through adding your login to the administrators again, change the service account back, etcSue
Will try that on Monday
Thank You
June 11, 2018 at 11:20 pm
This was removed by the editor as SPAM
June 13, 2018 at 10:13 am
Thanks for your help folks, it was indeed that user having removed access to admin users (BuiltinAdminsAreServerAdmins) and it was fixed by amending the ini file
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply