Hello,
I built a simple SSIS package using BIDS.
The package does a backup of a DB to a network share.
I imported the dtsx file into SQL Server using SQL Server WB (connected to
the SSIS server)
I created a SQL Agent job to run the SSIS package.
I have created a credential that maps to a domain account (BackupUser1). The
domain account has access to the network share: I verified it by running a
command line using the Run As and accessing the network share as that user.
Of course, I have a sql agent proxy associated to the credentials which is
assigned to run the job step that executes the SSIS package.
When the job runs it fails because of access denied to the network share.
To see what actually happened, I changed the permissions and the share (and
the NTFS permissions) to allow R/W access to 'everyone'.
I re-scheduled the job and this time it succeeds. I went over to the backup
file that was created when the SSIS package executed and discovered that the
owner (i.e. the creator) of the file is not the domain account (BackupUser1),
rather it's the data base server computer account name DBSRV$
this means that SQL Server Agent didn't impersonate the account defined by
the SQL Agent proxy.
I looked for information and wasn't able to find an answer...
A couple of things I made in the local security policy (user rights
assignment):
allowed the specified domain account the permission to log-on as a batch job
allowed the SYSTEM principal (the agent runs under LocalSystem) to replace
process level token (i.e. impersonate, at least as I understand this user
right)
Any thoughts, suggestion on why SQL Server Agent doesn’t actually use the
credentials associated with the proxy account to access the network share?
Thanks,
Asher
Hello Asher,
As for this issue, I've found your another new thread in the following
newsgroup:
Subject: Running SSIS pakcage as an agent job using a proxy
Date: Thu, 21 Sep 2006 02:58:02 -0700
Newsgroups: microsoft.public.sqlserver.server
I've posted my reply there with some suggestion. Please feel free to
followup there if you have any further finding or questions.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Asher,
As for this issue, I've found your another new thread in the following
newsgroup:
Subject: Running SSIS pakcage as an agent job using a proxy
Date: Thu, 21 Sep 2006 02:58:02 -0700
Newsgroups: microsoft.public.sqlserver.server
I've posted my reply there with some suggestion. Please feel free to
followup there if you have any further finding or questions.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label network. Show all posts
Showing posts with label network. Show all posts
Wednesday, March 21, 2012
Running SSIS as an Agent job using a proxy
Hello,
I built a simple SSIS package using BIDS.
The package does a backup of a DB to a network share.
I imported the dtsx file into SQL Server using SQL Server WB (connected to
the SSIS server)
I created a SQL Agent job to run the SSIS package.
I have created a credential that maps to a domain account (BackupUser1). The
domain account has access to the network share: I verified it by running a
command line using the Run As and accessing the network share as that user.
Of course, I have a sql agent proxy associated to the credentials which is
assigned to run the job step that executes the SSIS package.
When the job runs it fails because of access denied to the network share.
To see what actually happened, I changed the permissions and the share (and
the NTFS permissions) to allow R/W access to 'everyone'.
I re-scheduled the job and this time it succeeds. I went over to the backup
file that was created when the SSIS package executed and discovered that the
owner (i.e. the creator) of the file is not the domain account (BackupUser1),
rather it's the data base server computer account name DBSRV$
this means that SQL Server Agent didn't impersonate the account defined by
the SQL Agent proxy.
I looked for information and wasn't able to find an answer...
A couple of things I made in the local security policy (user rights
assignment):
allowed the specified domain account the permission to log-on as a batch job
allowed the SYSTEM principal (the agent runs under LocalSystem) to replace
process level token (i.e. impersonate, at least as I understand this user
right)
Any thoughts, suggestion on why SQL Server Agent doesnâ't actually use the
credentials associated with the proxy account to access the network share?
Thanks,
AsherHello Asher,
As for this issue, I've found your another new thread in the following
newsgroup:
Subject: Running SSIS pakcage as an agent job using a proxy
Date: Thu, 21 Sep 2006 02:58:02 -0700
Newsgroups: microsoft.public.sqlserver.server
I've posted my reply there with some suggestion. Please feel free to
followup there if you have any further finding or questions.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
I built a simple SSIS package using BIDS.
The package does a backup of a DB to a network share.
I imported the dtsx file into SQL Server using SQL Server WB (connected to
the SSIS server)
I created a SQL Agent job to run the SSIS package.
I have created a credential that maps to a domain account (BackupUser1). The
domain account has access to the network share: I verified it by running a
command line using the Run As and accessing the network share as that user.
Of course, I have a sql agent proxy associated to the credentials which is
assigned to run the job step that executes the SSIS package.
When the job runs it fails because of access denied to the network share.
To see what actually happened, I changed the permissions and the share (and
the NTFS permissions) to allow R/W access to 'everyone'.
I re-scheduled the job and this time it succeeds. I went over to the backup
file that was created when the SSIS package executed and discovered that the
owner (i.e. the creator) of the file is not the domain account (BackupUser1),
rather it's the data base server computer account name DBSRV$
this means that SQL Server Agent didn't impersonate the account defined by
the SQL Agent proxy.
I looked for information and wasn't able to find an answer...
A couple of things I made in the local security policy (user rights
assignment):
allowed the specified domain account the permission to log-on as a batch job
allowed the SYSTEM principal (the agent runs under LocalSystem) to replace
process level token (i.e. impersonate, at least as I understand this user
right)
Any thoughts, suggestion on why SQL Server Agent doesnâ't actually use the
credentials associated with the proxy account to access the network share?
Thanks,
AsherHello Asher,
As for this issue, I've found your another new thread in the following
newsgroup:
Subject: Running SSIS pakcage as an agent job using a proxy
Date: Thu, 21 Sep 2006 02:58:02 -0700
Newsgroups: microsoft.public.sqlserver.server
I've posted my reply there with some suggestion. Please feel free to
followup there if you have any further finding or questions.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
Running SSIS as an Agent job using a proxy
Hello,
I built a simple SSIS package using BIDS.
The package does a backup of a DB to a network share.
I imported the dtsx file into SQL Server using SQL Server WB (connected to
the SSIS server)
I created a SQL Agent job to run the SSIS package.
I have created a credential that maps to a domain account (BackupUser1). The
domain account has access to the network share: I verified it by running a
command line using the Run As and accessing the network share as that user.
Of course, I have a sql agent proxy associated to the credentials which is
assigned to run the job step that executes the SSIS package.
When the job runs it fails because of access denied to the network share.
To see what actually happened, I changed the permissions and the share (and
the NTFS permissions) to allow R/W access to 'everyone'.
I re-scheduled the job and this time it succeeds. I went over to the backup
file that was created when the SSIS package executed and discovered that the
owner (i.e. the creator) of the file is not the domain account (BackupUser1),
rather it's the data base server computer account name DBSRV$
this means that SQL Server Agent didn't impersonate the account defined by
the SQL Agent proxy.
I looked for information and wasn't able to find an answer...
A couple of things I made in the local security policy (user rights
assignment):
allowed the specified domain account the permission to log-on as a batch job
allowed the SYSTEM principal (the agent runs under LocalSystem) to replace
process level token (i.e. impersonate, at least as I understand this user
right)
Any thoughts, suggestion on why SQL Server Agent doesnâ't actually use the
credentials associated with the proxy account to access the network share?
Thanks,
AsherHello Asher,
As for this issue, I've found your another new thread in the following
newsgroup:
Subject: Running SSIS pakcage as an agent job using a proxy
Date: Thu, 21 Sep 2006 02:58:02 -0700
Newsgroups: microsoft.public.sqlserver.server
I've posted my reply there with some suggestion. Please feel free to
followup there if you have any further finding or questions.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
I built a simple SSIS package using BIDS.
The package does a backup of a DB to a network share.
I imported the dtsx file into SQL Server using SQL Server WB (connected to
the SSIS server)
I created a SQL Agent job to run the SSIS package.
I have created a credential that maps to a domain account (BackupUser1). The
domain account has access to the network share: I verified it by running a
command line using the Run As and accessing the network share as that user.
Of course, I have a sql agent proxy associated to the credentials which is
assigned to run the job step that executes the SSIS package.
When the job runs it fails because of access denied to the network share.
To see what actually happened, I changed the permissions and the share (and
the NTFS permissions) to allow R/W access to 'everyone'.
I re-scheduled the job and this time it succeeds. I went over to the backup
file that was created when the SSIS package executed and discovered that the
owner (i.e. the creator) of the file is not the domain account (BackupUser1),
rather it's the data base server computer account name DBSRV$
this means that SQL Server Agent didn't impersonate the account defined by
the SQL Agent proxy.
I looked for information and wasn't able to find an answer...
A couple of things I made in the local security policy (user rights
assignment):
allowed the specified domain account the permission to log-on as a batch job
allowed the SYSTEM principal (the agent runs under LocalSystem) to replace
process level token (i.e. impersonate, at least as I understand this user
right)
Any thoughts, suggestion on why SQL Server Agent doesnâ't actually use the
credentials associated with the proxy account to access the network share?
Thanks,
AsherHello Asher,
As for this issue, I've found your another new thread in the following
newsgroup:
Subject: Running SSIS pakcage as an agent job using a proxy
Date: Thu, 21 Sep 2006 02:58:02 -0700
Newsgroups: microsoft.public.sqlserver.server
I've posted my reply there with some suggestion. Please feel free to
followup there if you have any further finding or questions.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
Running SSIS as an Agent job using a proxy
Hello,
I built a simple SSIS package using BIDS.
The package does a backup of a DB to a network share.
I imported the dtsx file into SQL Server using SQL Server WB (connected to
the SSIS server)
I created a SQL Agent job to run the SSIS package.
I have created a credential that maps to a domain account (BackupUser1). The
domain account has access to the network share: I verified it by running a
command line using the Run As and accessing the network share as that user.
Of course, I have a sql agent proxy associated to the credentials which is
assigned to run the job step that executes the SSIS package.
When the job runs it fails because of access denied to the network share.
To see what actually happened, I changed the permissions and the share (and
the NTFS permissions) to allow R/W access to 'everyone'.
I re-scheduled the job and this time it succeeds. I went over to the backup
file that was created when the SSIS package executed and discovered that the
owner (i.e. the creator) of the file is not the domain account (BackupUser1)
,
rather it's the data base server computer account name DBSRV$
this means that SQL Server Agent didn't impersonate the account defined by
the SQL Agent proxy.
I looked for information and wasn't able to find an answer...
A couple of things I made in the local security policy (user rights
assignment):
allowed the specified domain account the permission to log-on as a batch job
allowed the SYSTEM principal (the agent runs under LocalSystem) to replace
process level token (i.e. impersonate, at least as I understand this user
right)
Any thoughts, suggestion on why SQL Server Agent doesn’t actually use the
credentials associated with the proxy account to access the network share?
Thanks,
AsherHello Asher,
As for this issue, I've found your another new thread in the following
newsgroup:
Subject: Running SSIS pakcage as an agent job using a proxy
Date: Thu, 21 Sep 2006 02:58:02 -0700
Newsgroups: microsoft.public.sqlserver.server
I've posted my reply there with some suggestion. Please feel free to
followup there if you have any further finding or questions.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
I built a simple SSIS package using BIDS.
The package does a backup of a DB to a network share.
I imported the dtsx file into SQL Server using SQL Server WB (connected to
the SSIS server)
I created a SQL Agent job to run the SSIS package.
I have created a credential that maps to a domain account (BackupUser1). The
domain account has access to the network share: I verified it by running a
command line using the Run As and accessing the network share as that user.
Of course, I have a sql agent proxy associated to the credentials which is
assigned to run the job step that executes the SSIS package.
When the job runs it fails because of access denied to the network share.
To see what actually happened, I changed the permissions and the share (and
the NTFS permissions) to allow R/W access to 'everyone'.
I re-scheduled the job and this time it succeeds. I went over to the backup
file that was created when the SSIS package executed and discovered that the
owner (i.e. the creator) of the file is not the domain account (BackupUser1)
,
rather it's the data base server computer account name DBSRV$
this means that SQL Server Agent didn't impersonate the account defined by
the SQL Agent proxy.
I looked for information and wasn't able to find an answer...
A couple of things I made in the local security policy (user rights
assignment):
allowed the specified domain account the permission to log-on as a batch job
allowed the SYSTEM principal (the agent runs under LocalSystem) to replace
process level token (i.e. impersonate, at least as I understand this user
right)
Any thoughts, suggestion on why SQL Server Agent doesn’t actually use the
credentials associated with the proxy account to access the network share?
Thanks,
AsherHello Asher,
As for this issue, I've found your another new thread in the following
newsgroup:
Subject: Running SSIS pakcage as an agent job using a proxy
Date: Thu, 21 Sep 2006 02:58:02 -0700
Newsgroups: microsoft.public.sqlserver.server
I've posted my reply there with some suggestion. Please feel free to
followup there if you have any further finding or questions.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
Tuesday, March 20, 2012
Running SQL Service under Network Service account
Microsoft recommends that you do not use the Network Service account to run the SQL Server service (see http://msdn2.microsoft.com/en-us/library/ms143504.aspx).
Can anyone tell me what the drawbacks are of doing this?
The Service account is a sysadmin on SQL Server so if I had another application running as the service account that application could authenticate to SQL Server as sysadmin.
When you run as local system then you can potentially harm the windows OS.
When you run as network Service then other services can potentially harm you.
HTH,
-Steven Gott
S/DET
SQL Server
Running SQL Server at Home
Hi All,
I'm trying to run a copy of Enterprise Manager at home. I have a small wireless network and would like to install it on either one machine (stand-alone) or run it on my network.
The machine it is installed on is running Windows 2000 and the remote machine is running Windows XP Home <<< not sure if SQL Server will run on this.
I tried installing it previously as stand-alone on the Win2K machine, but kept getting a "Server not found or access denied" message whenever I tried to register or connect.
Any ideas on how best to set it up at home would be greatly appreciated.
Thanks,
TimCreate an entry for your sqlserver in hosts (or lmhosts) file. This should help with name/ip resolution. Also, it might be better to setup sqlserver in mixed mode (i.e. allow sql authentication).|||I hope you installed the SQL Server and not the client components alone. Probably, you accepted default values. When you register you can use one of the following: a dot (.), or (local) - including parenthesis - or your workstation computer name. All these are valid in a standalone computer.
Please check your registry under HKEY_LOCAL_MACHINE\Microsoft\MSSQLSERVER\SUPERSOCK ETNETLIB\NP and if the value is like \\.\pipe\sql\query then probably you accepted default value for Named Pipe as well as the instance. If you have decided to install this as an instance then you may see like \\.\pipe\<instance_name>\sql\query then probably you installed as a server.
Also under HKEY_LOCAL_MACHINE\Microsoft\MSSQLSERVER\SUPERSOCK ETNETLIB\TCP, you can determine the port number. If it is 1433 then you used default port.
Please make sure that MSSQLServer Service is running. You can check this through the Services.msc. If you do not see MSSQLServer Service then you have installed the client components only and install the server components. Start SQL Query Analyzer and enter any of the following:
Assumption: Your computer name is PRECAMBRIAN
1) .
2) (local)
3) PRECAMBRIAN
4) PRECAMBRIAN,1433 (or whatever port number is)
5) PRECAMBRIAN\InstanceName
Enter your SA account's password and you should be able to connect.
I'm trying to run a copy of Enterprise Manager at home. I have a small wireless network and would like to install it on either one machine (stand-alone) or run it on my network.
The machine it is installed on is running Windows 2000 and the remote machine is running Windows XP Home <<< not sure if SQL Server will run on this.
I tried installing it previously as stand-alone on the Win2K machine, but kept getting a "Server not found or access denied" message whenever I tried to register or connect.
Any ideas on how best to set it up at home would be greatly appreciated.
Thanks,
TimCreate an entry for your sqlserver in hosts (or lmhosts) file. This should help with name/ip resolution. Also, it might be better to setup sqlserver in mixed mode (i.e. allow sql authentication).|||I hope you installed the SQL Server and not the client components alone. Probably, you accepted default values. When you register you can use one of the following: a dot (.), or (local) - including parenthesis - or your workstation computer name. All these are valid in a standalone computer.
Please check your registry under HKEY_LOCAL_MACHINE\Microsoft\MSSQLSERVER\SUPERSOCK ETNETLIB\NP and if the value is like \\.\pipe\sql\query then probably you accepted default value for Named Pipe as well as the instance. If you have decided to install this as an instance then you may see like \\.\pipe\<instance_name>\sql\query then probably you installed as a server.
Also under HKEY_LOCAL_MACHINE\Microsoft\MSSQLSERVER\SUPERSOCK ETNETLIB\TCP, you can determine the port number. If it is 1433 then you used default port.
Please make sure that MSSQLServer Service is running. You can check this through the Services.msc. If you do not see MSSQLServer Service then you have installed the client components only and install the server components. Start SQL Query Analyzer and enter any of the following:
Assumption: Your computer name is PRECAMBRIAN
1) .
2) (local)
3) PRECAMBRIAN
4) PRECAMBRIAN,1433 (or whatever port number is)
5) PRECAMBRIAN\InstanceName
Enter your SA account's password and you should be able to connect.
Subscribe to:
Posts (Atom)