SQLCMD: Getting Access Denied

• wak_no1

  SSCrazy

  Points: 2400

  More actions

  February 21, 2018 at 11:14 am

  #343719

  Hi
  Im running the following in a SQL agent job but realised the script im trying to call and execute, didn't. I ran the steps below outside of the agent job:

  
--allow advanced options on server
EXEC sp_configure 'show advanced options', 1
GO
--install changes
Reconfigure with override
GO
--Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--install changes
Reconfigure with override
GO

  declare @sql varchar(8000)

  SELECT @SQL='sqlcmd -E -S ' + @@SERVERNAME + ' -i \\' + cast(SERVERPROPERTY('MachineName') as varchar(max)) + '\C$\directory\test.sql'
EXEC xp_cmdshell @sql

  SELECT @SQL='sqlcmd -E -S ' + @@SERVERNAME + ' -i \\' + cast(SERVERPROPERTY('MachineName') as varchar(max)) + '\C$\directory\test.sql'
EXEC xp_cmdshell @sql

  --disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
--install changes
GO
Reconfigure with override
GO

  --disable advanced options on server
EXEC sp_configure 'show advanced options', 0
GO
--install changes
Reconfigure with override
GO


  I'm getting: Sqlcmd: Error: Error occurred while opening or operating on file \\servername\C$\directory\test.sql (Reason: Access is denied).

  The service account that's running sql has full read/write permissions on the share, I have full read/write permissions too but still get the error. I've verified the directory/file too.

  What other possible causes could there be?

• HanShi

  SSC-Dedicated

  Points: 33506

  More actions

  February 21, 2018 at 1:11 pm

  #1980676

  wak_no1 - Wednesday, February 21, 2018 11:14 AM

  I'm getting: Sqlcmd: Error: Error occurred while opening or operating on file \\servername\C$\directory\test.sql (Reason: Access is denied).

  The service account that's running sql has full read/write permissions on the share, I have full read/write permissions too but still get the error. I've verified the directory/file too.

  What other possible causes could there be?

  If you are running the code from a SQL Agent job, you'll need to grant permissions on the folder to the SQL Agent service account. That could well be another account than the SQL Server service account.
  Another option is to create credentials and use that to run the job (or jobstep).

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  February 21, 2018 at 1:50 pm

  #1980680

  wak_no1 - Wednesday, February 21, 2018 11:14 AM

  Hi
  Im running the following in a SQL agent job but realised the script im trying to call and execute, didn't. I ran the steps below outside of the agent job:

  
--allow advanced options on server
EXEC sp_configure 'show advanced options', 1
GO
--install changes
Reconfigure with override
GO
--Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--install changes
Reconfigure with override
GO

  declare @sql varchar(8000)

  SELECT @SQL='sqlcmd -E -S ' + @@SERVERNAME + ' -i \\' + cast(SERVERPROPERTY('MachineName') as varchar(max)) + '\C$\directory\test.sql'
EXEC xp_cmdshell @sql

  SELECT @SQL='sqlcmd -E -S ' + @@SERVERNAME + ' -i \\' + cast(SERVERPROPERTY('MachineName') as varchar(max)) + '\C$\directory\test.sql'
EXEC xp_cmdshell @sql

  --disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
--install changes
GO
Reconfigure with override
GO

  --disable advanced options on server
EXEC sp_configure 'show advanced options', 0
GO
--install changes
Reconfigure with override
GO


  I'm getting: Sqlcmd: Error: Error occurred while opening or operating on file \\servername\C$\directory\test.sql (Reason: Access is denied).

  The service account that's running sql has full read/write permissions on the share, I have full read/write permissions too but still get the error. I've verified the directory/file too.

  What other possible causes could there be?

  It looks like you are referencing the admin share C$. Not sure how you would have set permissions on it though. However, access to the admin shares is limited to those in the administrators group. Maybe try creating a regular share for the script.

  Sue

• wak_no1

  SSCrazy

  Points: 2400

  More actions

  February 22, 2018 at 12:53 am

  #1980724

  HanShi - Wednesday, February 21, 2018 1:11 PM

  wak_no1 - Wednesday, February 21, 2018 11:14 AM

  I'm getting: Sqlcmd: Error: Error occurred while opening or operating on file \\servername\C$\directory\test.sql (Reason: Access is denied).

  The service account that's running sql has full read/write permissions on the share, I have full read/write permissions too but still get the error. I've verified the directory/file too.

  What other possible causes could there be?

  If you are running the code from a SQL Agent job, you'll need to grant permissions on the folder to the SQL Agent service account. That could well be another account than the SQL Server service account.
  Another option is to create credentials and use that to run the job (or jobstep).

  I'm running it as me, i'm an admin on the server. I've also added the SQL Agent service account as admin but still get the error.

• wak_no1

  SSCrazy

  Points: 2400

  More actions

  February 22, 2018 at 1:34 am

  #1980728

  Sue_H - Wednesday, February 21, 2018 1:50 PM

  wak_no1 - Wednesday, February 21, 2018 11:14 AM

  Hi
  Im running the following in a SQL agent job but realised the script im trying to call and execute, didn't. I ran the steps below outside of the agent job:

  
--allow advanced options on server
EXEC sp_configure 'show advanced options', 1
GO
--install changes
Reconfigure with override
GO
--Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--install changes
Reconfigure with override
GO

  declare @sql varchar(8000)

  SELECT @SQL='sqlcmd -E -S ' + @@SERVERNAME + ' -i \\' + cast(SERVERPROPERTY('MachineName') as varchar(max)) + '\C$\directory\test.sql'
EXEC xp_cmdshell @sql

  SELECT @SQL='sqlcmd -E -S ' + @@SERVERNAME + ' -i \\' + cast(SERVERPROPERTY('MachineName') as varchar(max)) + '\C$\directory\test.sql'
EXEC xp_cmdshell @sql

  --disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
--install changes
GO
Reconfigure with override
GO

  --disable advanced options on server
EXEC sp_configure 'show advanced options', 0
GO
--install changes
Reconfigure with override
GO


  I'm getting: Sqlcmd: Error: Error occurred while opening or operating on file \\servername\C$\directory\test.sql (Reason: Access is denied).

  The service account that's running sql has full read/write permissions on the share, I have full read/write permissions too but still get the error. I've verified the directory/file too.

  What other possible causes could there be?

  It looks like you are referencing the admin share C$. Not sure how you would have set permissions on it though. However, access to the admin shares is limited to those in the administrators group. Maybe try creating a regular share for the script.

  Sue

  Ok, so I tested this. I've changed the SQLCMD bit:

  SELECT @SQL='sqlcmd -E -S ' + 'server\instance -i C:\directory\directory\script1.sql'
EXEC xp_cmdshell @sql

  SELECT @SQL='sqlcmd -E -S ' + server\instance -i C:\directory\directory\script2.sql'
EXEC xp_cmdshell @sql

  This now works, thanks.

  I'm going to get the AD guys to amend the SQL service and SQL agent accounts to allow them access to the admin share.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  February 22, 2018 at 2:05 am

  #1980729

  wak_no1 - Thursday, February 22, 2018 1:34 AM

  Sue_H - Wednesday, February 21, 2018 1:50 PM

  wak_no1 - Wednesday, February 21, 2018 11:14 AM

  Hi
  Im running the following in a SQL agent job but realised the script im trying to call and execute, didn't. I ran the steps below outside of the agent job:

  
--allow advanced options on server
EXEC sp_configure 'show advanced options', 1
GO
--install changes
Reconfigure with override
GO
--Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--install changes
Reconfigure with override
GO

  declare @sql varchar(8000)

  SELECT @SQL='sqlcmd -E -S ' + @@SERVERNAME + ' -i \\' + cast(SERVERPROPERTY('MachineName') as varchar(max)) + '\C$\directory\test.sql'
EXEC xp_cmdshell @sql

  SELECT @SQL='sqlcmd -E -S ' + @@SERVERNAME + ' -i \\' + cast(SERVERPROPERTY('MachineName') as varchar(max)) + '\C$\directory\test.sql'
EXEC xp_cmdshell @sql

  --disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
--install changes
GO
Reconfigure with override
GO

  --disable advanced options on server
EXEC sp_configure 'show advanced options', 0
GO
--install changes
Reconfigure with override
GO


  I'm getting: Sqlcmd: Error: Error occurred while opening or operating on file \\servername\C$\directory\test.sql (Reason: Access is denied).

  The service account that's running sql has full read/write permissions on the share, I have full read/write permissions too but still get the error. I've verified the directory/file too.

  What other possible causes could there be?

  It looks like you are referencing the admin share C$. Not sure how you would have set permissions on it though. However, access to the admin shares is limited to those in the administrators group. Maybe try creating a regular share for the script.

  Sue

  I'm going to get the AD guys to amend the SQL service and SQL agent accounts to allow them access to the admin share.

  You can't do that.  Like Sue said, only admins have access to the admin share.  You'd have to put the accounts into the local or domain admins group, which you really don't want to do.  Create a regular share instead, so that you can set proper permissions on it.

  John