Ask Your Question
0

Running sql scripts in puppet

asked 2018-01-19 13:13:17 -0500

athlonxl gravatar image

updated 2018-01-21 11:14:08 -0500

Hello today i am trying to get puppet to run my sql scripts. I have got the script to the point where it will run my sql script in sqcmd in puppet but all that does when execute is read my script and not execute it. When review it seems to be a user issue, My question being how to I get the following script when run to switch to another user in sqlcmd. This is being done on a window server.

class profiles::shares::sqloperator {

    $path = 'C:\dev\Scripts\Scripts\test.sql'
    $sqlcmd = "C:\\Program Files\\Microsoft SQL Server\\110\\Tools\\Binn\\sqlcmd.exe"
  exec { "adds an operator":
    command     => "\"${sqlcmd}\" -i ${path}",
    user => "Administrator",
  }
}

error that occurs when trying to switch users

image description

sql script that is being run by puppet:

/****** Object:  Operator [DBAs]    Script Date: 6/30/2014 2:16:03 PM ******/
EXEC msdb.dbo.sp_add_operator @name=N'DBAs', 
              @enabled=1, 
              @weekday_pager_start_time=0, 
              @weekday_pager_end_time=235959, 
              @saturday_pager_start_time=0, 
              @saturday_pager_end_time=235959, 
              @sunday_pager_start_time=0, 
              @sunday_pager_end_time=235959, 
              @pager_days=127, 


@email_address=test@test.com;random@email.com;notreal@email.com;random@email.
com',
              @pager_address=N'SQLAlerts@email.com', 
              @category_name=N'[Uncategorized]'
GO

If user is removed the puppet script will run but when it comes to executing the sql script correctly hiccups occur. When sql is run all it does is read the script and not execute any of the changes. It was discovered that the user it was executed on did not have the permissions. When run under another user it executed correctly and added my operators for dba. The goal is to Create an operatora (notification recipient) for use with alerts and jobs with puppet in sql.

edit retag flag offensive close merge delete

Comments

I'd use the exec's user attribute https://puppet.com/docs/puppet/latest/types/exec.html#exec-attribute-user instead of delegating rights _inside_ sqlcmd.exe. Are you definitely sure your command isn't executed? What are you doing at all? I have a hunch your task suffers a misconception.

Kai Burghardt gravatar imageKai Burghardt ( 2018-01-20 06:30:10 -0500 )edit

@Kai Burghardt I tried that but when run it puppet it says it cant change user in windows. What Im trying to do in my sql script is to add sql operators. The sql script itself has been tested on my window server and works correctly. Now all that left is to have puppet run the sql script.

athlonxl gravatar imageathlonxl ( 2018-01-20 20:25:15 -0500 )edit

SQL operators, so like operator overloading in a programming language? Is there no configuration file that's read when the server starts up? I mean, because your exec resource has no unless/onlyif condition, it'd execute _every_ time, which is _not_ part of the exercise.

Kai Burghardt gravatar imageKai Burghardt ( 2018-01-21 05:18:42 -0500 )edit

@Kai Burghardt question has been updated with more details on my error

athlonxl gravatar imageathlonxl ( 2018-01-21 11:03:40 -0500 )edit

Negative. It's not updated.

Kai Burghardt gravatar imageKai Burghardt ( 2018-01-21 11:10:45 -0500 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-01-23 14:32:52 -0500

athlonxl gravatar image

Figure out what i was doing wrong was not using the correct parameters when using sqlcmd. When i look up sqlcmd it showed me the correct parameters that included for username and password. Shown below is the code i used to execute my script correctly.

command => "${sqlcmd} -U ${username} -P ${password} -i ${path}",
edit flag offensive delete link more

Comments

1

Well, RTFM, except that you absolutely don't specify passwords on the command line (unless $password is a hash). And yet still your exec lacks of a onlyif/unless condition. It'd be executed every time Puppet runs. I mean, there's gotta be configuration file you want to distribute.

Kai Burghardt gravatar imageKai Burghardt ( 2018-01-23 18:08:59 -0500 )edit

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Question Tools

1 follower

Stats

Asked: 2018-01-19 13:13:17 -0500

Seen: 256 times

Last updated: Jan 23