gMSA's Appear UNSUPPORTED at Server Component Install Time!

PDinCAPDinCA Posts: 642 Silver 1
in SQL Backup
Just downloaded the latest DBA Bundle (on Jan 16th, 2018) and although the server component installer appeared to correctly recognize that SQL is using a gMSA, therefore disabled the password control, when one proceeds, a nasty error results. This carries with it the joy of being unable to re-install from the UI (to change the login), and, even further joy, after a Windows+X Uninstall of the Server components, still the refreshed UI won't allow a re-install.

Please fix the installer as it is clearly out of step with SQL's ability to use gMSA Windows Accounts...74i8c7fcuhux.png
Jesus Christ: Lunatic, liar or Lord?
Decide wisely...
Tagged:
· Share on Twitter

Answers

  • Dan BDan B Posts: 287 Gold 5
    Hi PDinCA,

    That doesn't sound like the most user friendly experience.

    Can I confirm that navigating to the install directory "C:\Program Files (x86)\Red Gate\SQL Backup 9" and running the SQBServerSetup.exe has got everything working?

    Or do you need assistance to get backup up and running?

    For resolving the issue I have added this to the open bug report to get SQL Backup working with gMSA which has reference SB-5745. I will update you on the progress of this.
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
    · Share on Twitter
  • PDinCAPDinCA Posts: 642 Silver 1
    Using the SQBServerSetup.exe, as I'm very accustomed to doing, is a worse experience...

    There are no passwords for gMSA logins, so when I use that dialog, I'm immediately confronted with a requirement for a login AND a password. "No can do..." is my response to that. Cancel and go back to good-old local Windows Login, which is a royal pain to set up in Windows Server 2016 (thanks MS!).

    I'm up and running, but I also had to negotiate the "not a server principal" when SQB was up and running with the local login and the SQL Service with the gMSA. So, I added the gMSA as a SQL Login and granted it sysadmin role. Backups still failed:
    1/16/2018 7:24:47 PM: Backing up master (full database) to: 
1/16/2018 7:24:47 PM:   G:\SQLBackups\master\FULL_<Server>_master_20180116_192447.sqb
 
1/16/2018 7:24:47 PM: Error 880: BACKUP DATABASE permission denied in database:  (master)
1/16/2018 7:24:47 PM: SQL error 15157: Setuser failed because of one of the following reasons: the database principal 'RS\XXXSQLService$' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.

    That's when I changed the SQL Service to also use the local admin login, and, per normal, everything just works.

    Despite Microsoft stating that for YEARS gMSAs can be used with SQL Server, the reality, unless (very likely) we missed some crucial configuration re the gMSA, is that gMSAs aren't really 100% compatible...

    We would dearly like to use them for security reasons. If we missed something (this is the first time using any gMSA for our I.T. Manager or myself, accidental DBA) we are all ears!
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
    · Share on Twitter
  • Dan BDan B Posts: 287 Gold 5
    Hi PDinCA,

    If your using the GSMA for the sql server account you need to setup the skip checks registry key.

    To do this:
    Open Registry Editor and locate HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\(local) or <instance name>
    Create a new DWORD type registry entry called SkipChecks and set the data value to 1.
    From the Windows Services panel, stop and restart the SQL Backup Agent service.
    To reinstate user rights checks, set the data value to 0 or delete the SkipChecks entry.

    You can set the Backup agent service to be a gMSA after the service is installed. Although each time you update you will face this same issue of not being able to put it into the installer.
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
    · Share on Twitter
  • PDinCAPDinCA Posts: 642 Silver 1
    Which begs the question, "When will the Product correctly support gMSA logins, for initial install and updates?"

    Although you present a workaround, you final sentence negates it on the grounds of practicality. I have over 20 separate boxes to attend to and faffing with the registry et al isn't my favorite activity, especially when you tell me I'll have the problem again on updates...

    Please accelerate full support of gMSA logins in at least THIS product. We attempted to use a gMSA with SQL Monitor and gave up that futile exercise too...
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
    · Share on Twitter
  • Dan BDan B Posts: 287 Gold 5
    Hi PDinCA,

    For clarity you wouldn't need to update the registry each time. However you would need to run the install pass in a windows account then change the service to run as a gMSA login which doesn't really change the point your making that it isn't fully supported at present.

    I will let the team know your thoughts on this and update you if I get any further response.
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
    · Share on Twitter
Sign In or Register to comment.