February 12, 2007 at 2:41 am
Hi Guys,
I have the following stored proc:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp__TNGAlert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp__TNGAlert]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[sp__TNGAlert]
@JobName NVARCHAR(4000),
@alert VARCHAR(50)
as
SET NOCOUNT ON
--
DECLARE @AlertDir NVARCHAR(4000)
DECLARE @ServerName VARCHAR(50)
DECLARE @FailureDate VARCHAR(50)
DECLARE @Error NVARCHAR(4000)
DECLARE @cmd SYSNAME
--
SET @AlertDir = ' >> \\BEPSQL01\SQLG\TNGLog\Alerts.txt'
SET @ServerName = @@ServerName
SET @FailureDate = (select getdate())
SET @Error = @ServerName + ' - ' + @JobName + ' ' + @alert + ' on ' + @FailureDate + @AlertDir
SET @cmd = 'echo ' + @error
--
EXEC master.dbo.xp_cmdshell @cmd
--
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
When I execute the script(EXEC sp__TNGAlert 'Backup System Database','Test Failure') in query analyzer it gives me the following error:
Logon Failure: Unknown user name or bad password
Can anyone pls help me on this?
Regards
February 15, 2007 at 8:00 am
This was removed by the editor as SPAM
February 15, 2007 at 12:54 pm
This is tricky.
I tried to run this line from the command line:
ECHO SOMESTRING \\MyComputer\MyShare\MyFolder\TEST.TXT
It works fine, a file TEST.TXT was created and records appended at the specified location on the share. I do have access to this share. The file owner is my Windows Domain login, in the Event Viewer the account that logged to the remote computer was a system account of my computer MYCOMPUTERNAME$
If I execute the same from the Query Analyzer using xp_cmdshell even without stored Procedure, again, in the Event Viewer it is MYCOMPUTERNAME$ who is logging to remote computer, but the outcome of the statement is Access Denied. On my computer SQL Server is running under Local System account.
Moreover, when I tried to run from QA the same statement from SQL Server running on the domain account that has admin rights in both SQL Server and remote computer and put results on the share that has admin access for this domain account, access was denied. When I checked the sessions who was connecting to the share, it was that SQL Server's COMPUTERNAME$ account, not a domain account!
Only finally when I gave share and folder permissions to EVERYONE to write to this share so COMPUTERNAME$ will be able to write to it, the statement from QA succeeded!
The bottom line: for your SP to write to the network share, you need to give your SQL Server's computer's system account rights to the network share. Since it is not possible, you have to give rights to Everyone which is not recommended.
Workaround: write to the same computer and copy over the network using Windows Task Scheduler.
Regards,Yelena Varsha
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply