Executing GAM tasks within SQL Server Integration Services (SSIS) packages

[Jeff Puls]

Hoping someone out there can share some best practices on how this could work...

We use SSIS for a lot of our ETL work and we're looking at the potential to use it to execute GAM commands as part of scheduled tasks on one of our servers. The gist is that we would be generating CSV files and then use them with GAM commands called from a SSIS "Execute Task" workflow. We've done things like this before using PowerShell to maintain records in AD, for example. The difference here is that we would be performing an API call to our Google domain, and I want to make sure we've thought through the credential/permission components necessary for GAM to run unattended. We would be scheduling the package to run using SQL Server Agent using a local domain service account as a proxy.

We haven't yet tried to install GAM on that server (MS Server 2012 R2). Before we do, I'm wondering what people would suggest for handling the user/application permissions and scopes? My gut tells me that we would want to create a use-specific account in our Google domain as a service account to be used in the installation of GAM on our server. If that is what you recommend, when specific minimum permissions are necessary on the account in order to execute GAM commands for

gam update group

, for example?

[Ross Scroggs]

Jeff,

These are the scopes for client access:

[*]  0)  Classroom API - counts as 5 scopes

[*]  1)  Cloud Print API

[*]  2)  Data Transfer API (supports readonly)

[*]  3)  Directory API - Chrome OS Devices (supports readonly)

[*]  4)  Directory API - Customers (supports readonly)

[*]  5)  Directory API - Domains (supports readonly)

[*]  6)  Directory API - Groups (supports readonly)

[*]  7)  Directory API - Mobile Devices (supports readonly and action)

[*]  8)  Directory API - Notifications

[*]  9)  Directory API - Organizational Units (supports readonly)

[*] 10)  Directory API - Resource Calendars (supports readonly)

[*] 11)  Directory API - Roles (supports readonly)

[*] 12)  Directory API - User Schemas (supports readonly)

[*] 13)  Directory API - User Security

[*] 14)  Directory API - Users (supports readonly)

[*] 15)  Email Settings API - Delegation

[*] 16)  Group Settings API

[*] 17)  License Manager API

[ ] 18)  Pub / Sub API

[*] 19)  Reports API - Audit Reports

[*] 20)  Reports API - Usage Reports

[ ] 21)  Reseller API

[*] 22)  Site Verification API

[*] 23)  Vault Matters and Holds API (supports readonly)

These are the scopes for service account access:

 Scope: https://mail.google.com/                                     PASS

 Scope: https://www.googleapis.com/auth/activity                     PASS

 Scope: https://www.googleapis.com/auth/calendar                     PASS

 Scope: https://www.googleapis.com/auth/drive                        PASS

 Scope: https://www.googleapis.com/auth/gmail.settings.basic         PASS

 Scope: https://www.googleapis.com/auth/gmail.settings.sharing       PASS

 Scope: https://www.googleapis.com/auth/plus.me                      PASS

You only need service account access if you're going to manage things (drive, calendar, gmail) for users.

Ross

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.
To post to this group, send email to google-apps-manager@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/982c4c99-f811-4ff5-a870-fbf23327877b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Ross Scroggs

ross.s...@gmail.com

[Jeff Puls]

Ross, thanks. I'm over that hurdle, but I'm still having a permissions issue when trying to execute a batch file as a scheduled job with SSIS . It appears that GAM isn't able to execute the commands... possibly a "SECTION" issue? I can successfully run the batch file when logged into the server as myself (my account was used to do the initial install of both GAM and GAMADV-X), but I need to be able to execute a scheduled job that does not require me to login and initiate it. In other projects on which I've worked, I've used a Windows proxy account that has permissions to access any needed files... similar to this explanation from MS. In this case, even impersonating myself with this task will fail... it is only successful when I am physically logged into the server.

Is there syntax that could be added to the gam command that would provide the necessary context to successfully execute as a particular user? I read a little about "SECTION", but I'm not quite sure if that could apply here.

The batch file contents:

gam update group csvkmd \SSIS\RoleResourceMatrix\GAM\DataFiles\ToBeProcessed\GoogleGroupMembership.csv keyfield GroupEmail datafield EmailAddress sync member csvdata EmailAddress

To post to this group, send email to google-ap...@googlegroups.com.

Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/982c4c99-f811-4ff5-a870-fbf23327877b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Ross Scroggs

ross.s...@gmail.com

[+KimNilsson]

Is it a scheduled run?

I do that all the time.

But I, of course, set the scheduled batch to run "as if I'm logged in", even though I am not.

[Jeff Puls]

Yes, it is a scheduled task that is executed via SQL Server Agent (not the Windows Task Scheduler). The task is being executed as a Windows user with admin permissions and for whom the gam.cfg file has been generated according to the directions located in GamConfig.txt. The syntax used for that step was:

gam select default verify

I should have also mentioned that I'm using GAMADV-X. The workflow looks like this:

Do a bunch of SQL stuff to identify desired group membership--> Export a CSV --> Execute the GAMADV-X command to sync group membership via batch file --> Move the CSV and rename it --> Rinse and repeat nightly

These steps are all built into a SQL Server Integration Services (SSIS) package that is executed using the SQL Server Agent. We have many jobs that run this way (including some that have executable batch files like this). There are always Windows permissions to consider, but in the case of GAMADV-X, there must be another layer that is getting in the way. In fact, I've run an almost identical process using traditional GAM with no problems. The reason I switched to GAMADV-X was due to the functionality Ross Scroggs pointed me to in this post. 

Short of bailing out and starting over with traditional GAM, I'm not sure what else might work. 

[Kim Nilsson]

GAMADV-X needs access to the gam.cfg and the gamcache folder. Those are normally in a "hidden" dot-folder in the  user directory, separate from the GAMADV-X binary. This requires the process that runs GAMADV-X to have access to that separate directory.

Or, maybe Ross can tell you how to configure GAMADV-X in a way so that all files are in the same directory. I'm pretty sure that there are such options you can use on the command line. 

[Tim Cooper]

Hi Jeff,

We have this working as you described.

We have used SSIS to export and create the CSV's from our SQL source, and then use a PowerShell Script to complete the syncs with GAM.  

Note I have found we have had to call the PS script via a batch script in SSIS to get it to work nicely as you need to override the default PS execution policies.

Have attached the ps script (extension renamed .123) and the batch script (extension renamed .456) if that helps you.

We have a number of linked SSIS packages that generate the list of groups to update and use different logic, depending on the group.

The packages are scheduled via SQL Server Agent Job as you mentioned.

Let me know if you need anything further and hope this helps.

Cheers

Tim

[Jeff Puls]

Hi Tim and thanks for the reply. Are you using standard GAM or another flavor? Assuming standard GAM based on the script? I'm just checking because I've experienced different behavior when it comes to running jobs using SQL Agent. GAMADV-X, for example, makes use of the gam.cfg file that can, among other things, allow for a multi-user/multi-domain set up. That feature provides a lot of flexibility with complex environments, but I believe that is what was tripping me up when trying to execute via SSIS scheduled job.

Ross has been very helpful with troubleshooting this with me, and the solution that seems to be working for now is to copy the contents of .gam from a known working user into the C:\Users\Default\.gam folder. After digging WAY into the logs of my failed jobs, I was able to see that scheduled SSIS job was somehow looking to that location for gam.cfg even though the job step was being executed by a different user.

Ross also put me on to what could be the ultimate solution of making use of the system environmental variable GAMCFGDIR. The idea would be to create a new folder at the root (something like C:\GAMCONFIG) with all of the auth and config files copied into it and then create a system environmental variable called GAMCFGDIR that points to that directory. GAMADV-X would then evaluate the contents of the .cfg file at that central location for all subsequent GAM command. I did try this method and was successful in executing the SSIS packages from within the Visual Studio environment regardless of the user... but the scheduled job still failed. When I used the method in the paragraph above, the scheduled job worked fine.

These are my experiences for what is worth.

Ross - hopefully my explanation of this didn't muddy the waters even more. Feel free to set the record straight if I misspoke.

[Jeff Puls]

So a quick update to my post below. I have successfully used the system environment variable approach mentioned below. My issue was user error... I had inadvertently created a user variable and not a system variable. Thanks to Ross for pointing me in the right direction.

I am far from an expert in any of these steps, but here is a summary of the steps I took. If you would like to use a single gam.cfg file with GAMADV-X that is independent of which user context is used to execute commands, you can:

1. Create a centrally-available folder (like C:\GAMCONFIG).
2. Copy the contents from a known-working user's .gam folder into C:\GAMCONFIG.
3. Be sure to validate that C:\GAMCONFIG\gam.cfg is configured as required. You may want to pay attention to any of the parameters that reference folder/file paths so they are pointing to the correct place in your new folder. (see this documentation for more info)
4. Create a system environmental variable called GAMCFGDIR (all caps) and point it to your new folder C:\GAMCONFIG 
5. Log off the device and back on again (apparently this step is needed in order to complete the creation of the system variable).
6. Rename your user's .gam folder to something else like .gam-old so that you can test without destroying your known-working user settings.
7. Test a GAMADV-X command.

Thanks to all who helped troubleshoot this with me.

[Tim Cooper]

Hi Jeff,

I believe that on this server it is standard GAM, however as we have separate G domains for our staff & students, we have scripts using GAMADV-X.  I'll look at them today, although I believe these are called via scheduled tasks, rather than SSIS.

It looks like you might have resolved it with Ross, but if I find anything, I'll let you know.

I do recall taking some time to get our scheduling and permissions right, but they have been working for a long time now so details of the initial issues are vague now.

Thanks for sharing your experience.

Cheers,

Tim