13 Replies Latest reply on Jun 12, 2013 3:47 PM by Bill Robinson

    Automation Principal for MSSQL execution

    Krishna Vaddadi

      Hi

      I have created an automation principal for use in MSSQL DB and verified that it’s working absolutely fine. However noticed that this account invariably needs to be added to the local administrative group in the box (where we access MSSQL client) for automation principal to work. Only assigning Log on as Batch Job privileges is not working, if I remove this user from local Administrative group the script times out and fails. Am I missing anything, I thought for APs if the user is granted batch job privilege it should be enough

        • 1. Re: Automation Principal for MSSQL execution
          Siddu angadi

          In ordered to execute any script on windows you need to have admin rights.

           

          Thanks

          Siddu

          • 2. Re: Automation Principal for MSSQL execution
            Krishna Vaddadi

            Hi Siddu angadi

            Thanks for the reply however what I am trying to understand is that this user (in this intermediate box) is merely launching sqlcmd.exe to execute the DB commands elsewhere and also since this is a trusted connection source for the database, so DB is not actually worried about user privileges. What is then causing BladeLogic to worry for this user to be in Administrative group to merely launch the sqlcmd utility locally in this box? The documentation also states that if user is granted logon as batch job privilege then commands should work and also sqlcmd utility works fine when we execute the same commands externally, hence wanted to know if automation principals will always require a user added into local administrative group?

            • 3. Re: Automation Principal for MSSQL execution
              Bill Robinson

              Can you clarify what system you are are adding the AP user to the local admins group?  on the 'intermediate' system or the target system you are installing on?

               

              why are you even using this intermediate system?  what aren't you targeting the system you are installing sqlserver on directly ?

              • 4. Re: Automation Principal for MSSQL execution
                Krishna Vaddadi

                Hi Bill

                Thank you for replying, we have installed MSSQL client (Sqlcmd) on a windows 2008 server and created an automation principal using a common network service account (this network service account has access to different MSSQL databases [different projects and teams]).

                So on this intermediate system I am noticing that for automation principal to work, I am having to add this service account user in the local Administrative group for BladeLogic being able to execute the DB commands. Though I have granted Logon as batch privileges, unless I am adding in local Administrator group the DB packages are not working.

                This is where I am not clear as to why is BladeLogic expecting this user to have administrator privileges while Sqlcmd utility works fine when launched externally. Network team has raised an exception about this user requiring Administrative privileges on this intermediate box, which is not required per se

                This network service account is common to different projects / teams, it is basically controlled by the DBA group and hence we have defined a common account and provided a common platform for different projects to execute their DB scripts on their respective servers. (Business use case being that couple of DB executions may be common/span different teams in such case it’s just a matter of diverting the execution of scripts to the required team’s DB instance)

                Hope this helps, glad to hear for any further information required

                • 5. Re: Automation Principal for MSSQL execution
                  Bill Robinson

                  if the AP user is not in the local admins group, what group is it in?  does that group have access to this target system?  can you login to the target system and run the same sqlcmd that you are running ?

                  1 of 1 people found this helpful
                  • 6. Re: Automation Principal for MSSQL execution
                    Krishna Vaddadi

                    Hi Bill

                    The AP user is a network service account and hence does not actually belong to any groups in the target box except that we have granted "Logon as Batch job" privileges in the target box. Since the AP was not working, I have added this user to the local Administrators group and seeing that it’s working fine. Are you explaining that the user in practical case has to belong to some group in target box for the AP to work on this target box? Also does this user need to have remote login permissions on a target box to be able to launch utilities (sqlcmd) from that box?

                    Many Thanks

                    Krishna

                    • 7. Re: Automation Principal for MSSQL execution
                      Bill Robinson

                      If you have a domain user account that is not part of any local groups, either directly or indirectly i don't see how you expect to be able to access the system.  you probably don't need to put the account in the local admins - power users or users should be sufficient for what you are doing - have you tried either of those?

                       

                      you should not need to ability to logon interactively - that was just see if you could manually perform the same actions you are attempting via bsa.

                      • 8. Re: Automation Principal for MSSQL execution
                        Krishna Vaddadi

                        Yes Bill I have verified that adding this user to Users group / Power Users group does not help any, and we get same the warning every time and the job times out without getting executed.

                        Warning: Error initializing node - Error buffer: /C/Windows/rsc/secure: Permission denied

                        Adding only to Administrators group it works fine. I checked now that the user is part of "Domain Users" in the network and this group is a part of Users group in this target box. I remember reading in KB (somewhere) related to this issue where-in if the domain user is accessing a target through one of the domain groups then permissions will not work, it is required for the domain user to belong directly to one of the local groups in the target box, please correct my understanding if I am mixing up something else, based on this I inferred that user has to be added to local group(s) in the box. However requirement to add it in local Administrator group is not being preferred and I am failing to understand the reason for this. Let me know if I need to change any additional group policies for adding this user in Users group and check that AP works

                        Thanks & Best Regards

                        Krishna

                         

                        • 9. Re: Automation Principal for MSSQL execution
                          Bill Robinson

                          can you add permissions on the secure file so whatever local group you put this user in can read the file ?

                          • 10. Re: Automation Principal for MSSQL execution
                            Krishna Vaddadi

                            Hi Bill

                            I have modified the permissions from Security tab (windows explorer) for both secure and securecert and assigned this user all permissions on these files. In fact did the same this on rsc folder itself, restarted the rscd service. This user I have currently added in the local Users group. Alas it did not help. But this time it fails definitely throwing a warning "Failed to receive a heartbeat within 270 seconds."

                            Thanks & Regards

                            Krishna

                            • 11. Re: Automation Principal for MSSQL execution
                              Krishna Vaddadi

                              Hi Bill

                              I have modified the permissions from Security tab (windows explorer) for both secure and securecert and assigned this user all permissions on these files. In fact did the same this on rsc folder itself, restarted the rscd service. This user I have currently added in the local Users group. Alas it did not help. But this time it fails definitely throwing a warning "Failed to receive a heartbeat within 270 seconds."

                              Thanks & Regards

                              Krishna

                              • 12. Re: Automation Principal for MSSQL execution
                                Krishna Vaddadi

                                By the way is Automation Principals the default mode of doing MSSQL deployments, can't I simply pass the DB credentials and execute the DB scripts (Like for Oracle)

                                • 13. Re: Automation Principal for MSSQL execution
                                  Bill Robinson

                                  you can use whatever account you want here - but that account needs to be able to have access to the files in the rsc directory.