Execute Batch File Windows 2008/Sql 2005 - Error !!!

Question

Execute Batch File Windows 2008/Sql 2005 - Error !!!

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
August 7, 2011 at 4:59 am

#241428

I have sql 2005 installed on windows 2008( i know not the best configuration but this is what i received), i have a batch and when i click on it i am able to execute it when i try to run through management studio or job i get the following error msg 'C:\Temp_PowerShell_Scripts\Ping_SqlServer.ps1 : TRAPPED: Access is denied. (Exc". Ping_sqlserver.ps1 is the powershell script wrapped inside a batch file. i tried giving exclusive permissions to the folders (even though sql service account is part of admin) but still same error. Can someone please point me what am i missing h ere?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 1

Try this. using the ID that runs SQL Server log onto the server and navigate to the folder in which that file is on. I bet you get a 'you need access' to this folder. Simply click through that warning. I have had this exact same problem in Win2008. You have to physically navigate to the folder so it authorizes your access eventhough the ID has access to it and you are an Admin on the server. Drives me absolutely nuts.

Lowell SSC Guru Points: 323518 More actions · Answer 2

Marcus has hit the core issue, i think: it's all about permissions, and the service running the SLq Service account doesn't have what is required.

the fastest, easiest fix is...

don't run PowerShell from xp_cmdShell.

SQL is kind of designed to handle all security internally via logins and users...but that's only for objects inside the databases and the TSQl structure...when you try to step outside of the database, security limits you to a specific account.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 3

Lowell (8/8/2011)
Marcus has hit the core issue, i think: it's all about permissions, and the service running the SLq Service account doesn't have what is required.
the fastest, easiest fix is...
don't run PowerShell from xp_cmdShell.
SQL is kind of designed to handle all security internally via logins and users...but that's only for objects inside the databases and the TSQl structure...when you try to step outside of the database, security limits you to a specific account.

I totally agree with you, what would you do with sql 2005 on a win 2008 box?i need a job( can be even windows job) to run every 5 mins?

Lowell SSC Guru Points: 323518 More actions · Answer 4

Windows Task Scheduler.

you can set a job to repeat on a schedule, but more importantly, you can decide what user context to run the command as much easier.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

Lowell (8/8/2011)
Windows Task Scheduler.
you can set a job to repeat on a schedule, but more importantly, you can decide what user context to run the command as much easier.

The problem with windows scheduler is that the min recurrence could only be once a day and i need something to run every 5 mins?

Lowell SSC Guru Points: 323518 More actions · Answer 6

no the task scheduler has an option to repeat multiple times:

you have to dig intot eh details just a little here:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

Lowell (8/9/2011)
no the task scheduler has an option to repeat multiple times:
you have to dig intot eh details just a little here:

My bad, i tried on windows2003, but yeah on 2008 you could schedule it for every 5 minutes. Thanks