Bulk insert Error code 5(Access denied)

Bulk insert Error code 5(Access denied)

Post by Gerry Viato » Fri, 25 Oct 2002 05:04:15



Hi all,

Get the following error on bulk insert?

Could not bulk insert because file 'C:\temp\tmpfile.txt' could not be
opened. Operating system error code 5(Access is denied.).

This is what i try to run:
**********************************************
exec bulkinsert 'MyDatabase','MyTable','C:\temp\tmpfile.txt','|'

This is Stored Procedure:
***********************************************
CREATE Procedure BulkInsert




           AS




            WITH
         (
              ROWTERMINATOR = !\n! ,

              FIRSTROW = 2
           )'






GO

Thanks for any help
Gerry

 
 
 

Bulk insert Error code 5(Access denied)

Post by Brad » Fri, 25 Oct 2002 05:39:45


It sounds as though you do not have OS permissions to access the file you're
trying to.  Check this, and then get back to us.

Best Regards,
Brad


> Hi all,

> Get the following error on bulk insert?

> Could not bulk insert because file 'C:\temp\tmpfile.txt' could not be
> opened. Operating system error code 5(Access is denied.).

> This is what i try to run:
> **********************************************
> exec bulkinsert 'MyDatabase','MyTable','C:\temp\tmpfile.txt','|'

> This is Stored Procedure:
> ***********************************************
> CREATE Procedure BulkInsert




>            AS




>             WITH
>          (
>               ROWTERMINATOR = !\n! ,

>               FIRSTROW = 2
>            )'






> GO

> Thanks for any help
> Gerry


 
 
 

Bulk insert Error code 5(Access denied)

Post by Gerry Viato » Fri, 25 Oct 2002 21:47:57


Hi

thanks for the help,

I am Administrator, full access.

Thanks
Gerry


> It sounds as though you do not have OS permissions to access the file
you're
> trying to.  Check this, and then get back to us.

> Best Regards,
> Brad



> > Hi all,

> > Get the following error on bulk insert?

> > Could not bulk insert because file 'C:\temp\tmpfile.txt' could not be
> > opened. Operating system error code 5(Access is denied.).

> > This is what i try to run:
> > **********************************************
> > exec bulkinsert 'MyDatabase','MyTable','C:\temp\tmpfile.txt','|'

> > This is Stored Procedure:
> > ***********************************************
> > CREATE Procedure BulkInsert




> >            AS




> >             WITH
> >          (
> >               ROWTERMINATOR = !\n! ,

> >               FIRSTROW = 2
> >            )'






> > GO

> > Thanks for any help
> > Gerry

 
 
 

Bulk insert Error code 5(Access denied)

Post by Gerry Viato » Fri, 25 Oct 2002 23:00:40


Hi all,

Will I think I sovled the problem but not sure if I understand.

I will do my best to explain.

On the Server I  right clicked my computer and clicked Manage. Under shared
Folders
and the share (name is "Text")  which is the following  'C:\HoldtextFiles\'
Folder.

Under Security tab I have two users and both have Administraor Privledges,
Now I added
Administrators group to it and PRESTO it worked. Ok I don't understand.
If my user name member of has Administraors then why won't it work?

Thanks again for your help
Gerry


> Hi all,

> Get the following error on bulk insert?

> Could not bulk insert because file 'C:\temp\tmpfile.txt' could not be
> opened. Operating system error code 5(Access is denied.).

> This is what i try to run:
> **********************************************
> exec bulkinsert 'MyDatabase','MyTable','C:\temp\tmpfile.txt','|'

> This is Stored Procedure:
> ***********************************************
> CREATE Procedure BulkInsert




>            AS




>             WITH
>          (
>               ROWTERMINATOR = !\n! ,

>               FIRSTROW = 2
>            )'






> GO

> Thanks for any help
> Gerry

 
 
 

Bulk insert Error code 5(Access denied)

Post by Gerry Viato » Sat, 26 Oct 2002 04:00:55


Hi Bill Thanks for the information and documentation.

I checked a few things out.

My username which belongs to the sysadmin role and when I run the procedure
that you included
right before the Exec stored procedure says "You are not a sysadmin.....".

So what do I need to change?

I will try and give you some settings here:

SQLServer2000 is on a Windows2000 Server
          Logged into Domain as Administrator on that machine my user
account also
          has Administraors Access.
SQLServer startup account starts with a domain\Admin account and password

In SQLserver I have a SQL account which belongs to
    system Admin server role
    security Admin server role
    server Admin server role
    bulk insert Admin server role

Under the tab database access I have full access to all databases

One more thing I ran the Create Table #srvrolemember query.
Where does this reside? Where did I create this?
And how do I remove?

Thanks
Gerry



Quote:> Hi Gerry,

> Here is what should be the problem.

> From BOL "Transact-SQL Reference" chapter, "EXECUTE" topic, "Permissions"
> section

(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsql...
Quote:> _ea-ez_05ro.asp):
> {
> EXECUTE permissions for a stored procedure default to the owner of the
> stored procedure, who can transfer them to other users. **Permissions to
> use the statement(s) within the EXECUTE string are checked at the time
> EXECUTE is encountered, even if the EXECUTE statement is included within a
> stored procedure.** When a stored procedure is run that executes a string,
> permissions are checked in the **context of the user who executes the
> procedure**, not in the context of the user who created the procedure.
> } (my emphasis)

> From BOL "Transact-SQL Reference" chapter, "xp_cmdshell" topic, "Remarks"
> section
> (http://msdn.microsoft.com/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp):
> {
> xp_cmdshell will be executed under the security context in which the SQL
> Server service is running. When the user is **not** a member of the
> **sysadmin** group, xp_cmdshell will impersonate the **SQL Server Agent
> proxy account**, which is specified using xp_sqlagent_proxy_account.
> } (my emphasis)

> And although the BOL "Administering SQL Server" chapter, "Adding a Member
> to a Predefined Role" topic, "Fixed Server Roles" section

(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/admi...

- Show quoted text -

> d_security_6ndx.asp) says:
> {
> Windows NT 4.0 or Windows 2000 users who are members of the
> BUILTIN\Administrators group are members of the sysadmin fixed server role
> automatically.
> }
> that does **not** mean that an NT administrator will always become a
> sysadmin. For example, see Q263712 "INF: How to Impede Windows NT
> Administrators from Administering a Clustered [sic - billhol] SQL Server"
> (http://support.microsoft.com/support/kb/articles/q263/7/12.asp). Thus it
> is important to verify whether the user (who was running that stored
> procedure) is a member of sysadmin fixed server role in order to determine
> the security context of the stored procedure's EXEC(...xp_cmdshell...)
> statement. Here is one approach (that should be run while logged in to SQL
> Server as that user):
> --
> CREATE TABLE #srvrolemember
> (
> ServerRole SYSNAME
> , MemberName SYSNAME
> , MemberSID VARBINARY(85)
> )
> INSERT #srvrolemember
> EXEC master..sp_helpsrvrolemember 'sysadmin'



> FROM #srvrolemember
> WHERE MemberName = SUSER_SNAME()


> PRINT 'You are a sysadmin. SQL Server will use the SQL Server startup
> account''s NT permissions.'
> ELSE
> PRINT 'You are not a sysadmin. SQL Server will use the SQL Server Agent
> proxy account''s NT permissions.'
> --

> Thus the permissions of the NT account (used to log into SQL Server) does
> not matter too much (apart from determining role membership). In contrast,
> what needs to be carefully considered is either the NT permissions for
NT's
> SQL Server service startup account or the NT permissions for NT's SQL
> Server Agent proxy account (and the applicable NT account depends upon the
> role membership of a login).

> As an alternative to the above deductive approach, you could determine the
> account empirically by running the attached Windows 2000 Resource Kit's
> whoami.exe via xp_cmdshell (perhaps put it in C:\ and specify that path
(to
> whoami.exe) within xp_cmdshell's command-line string.

> Most security issues are convoluted (IMHO <g>), but there are important
> reasons for what appears to be obfuscation: We cannot allow people to
shell
> out from SQL Server (using the security context of SQL Server) unless they
> are first identified by SQL Server to be a sysadmin. And we cannot allow a
> user to dynamically execute T-SQL (within a stored procedure or otherwise)
> unless we first ensure they have permissions to execute. Impersonation on
> behalf of a client acount, and the ability (or inability) of that client
> account to logon locally to the SQL Server box are yet other issues <g>.

> Thanks,

> Bill Hollinshead
> Microsoft, SQL Server

> This posting is provided "AS IS" with no warranties, and confers no
> rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

Bulk insert Error code 5(Access denied)

Post by Bill Hollinshead [MS » Sat, 26 Oct 2002 05:51:28


Hi Gerry,

You can spit that T-SQL into two steps:
--
SELECT SUSER_SNAME()
EXEC master..sp_helpsrvrolemember 'sysadmin'
--
Make sure the account returned by SUSER_SNAME is what you expect, and make
sure it is returned within sp_helpsrvrolemember. Also see whether
BUILTIN\Administrators is present.

Here's another method:
--
SELECT IS_SRVROLEMEMBER('sysadmin')
--
If that returns 1 then you are a sysadmin, otherwise you aren't <g>.

Also try the empirical method (i.e., use whoami.exe from my last post).

If still having problems:
Was the master database restored from another server?
Were NT account names recently changed?
Have you checked NT permissions for the current SQL Agent proxy account
(search BOL for >xp_sqlagent_proxy_account<)?

Also, consider that an NT account (whether an administrator or not) that
has Access is Denied (at the share level or the file level), then NT will
always deny that access (even if a member of administrators, who can remove
<g> their own access is denied).

#srvrolemember  is a temporary table that will be automatically removed
when the connection that created it is closed (i.e., you don't need to
remove it, or, you can explicitly DROP TABLE #srvrolemember, but if the it
temporary table is already dropped then that DROP will return an error 3701
which is OK/expected).

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

Bulk insert Error code 5(Access denied)

Post by Gerry Viato » Sun, 27 Oct 2002 00:31:39


Thanks again for your help Bill

My username is returned when I run.
     SELECT SUSER_SNAME()
      EXEC master..sp_helpsrvrolemember 'sysadmin'

Yes  BUILTIN\Administrators is present.

Also running this returns 1.
      SELECT IS_SRVROLEMEMBER('sysadmin')

No, the master was not restored from another server.
No, NT accounts were not changed
   The only thing that was changed was we had to remove the group
"OurDomainName\Administrators" from
    all shared Folders. And then I just added my name to Security\Name. This
is were I am still having problems.
  I gave my Name access to everyhting but the kitchen sink.  And  I Am the
Admin for the Server and
 PDC Server.

Now when I run
         EXEC master.dbo.xp_sqlagent_proxy_account N'GET'

nothing is returned?

Also on the shared folder, denied access is not checked.

Again thanks for your time and help.

Gerry



Quote:> Hi Gerry,

> You can spit that T-SQL into two steps:
> --
> SELECT SUSER_SNAME()
> EXEC master..sp_helpsrvrolemember 'sysadmin'
> --
> Make sure the account returned by SUSER_SNAME is what you expect, and make
> sure it is returned within sp_helpsrvrolemember. Also see whether
> BUILTIN\Administrators is present.

> Here's another method:
> --
> SELECT IS_SRVROLEMEMBER('sysadmin')
> --
> If that returns 1 then you are a sysadmin, otherwise you aren't <g>.

> Also try the empirical method (i.e., use whoami.exe from my last post).

> If still having problems:
> Was the master database restored from another server?
> Were NT account names recently changed?
> Have you checked NT permissions for the current SQL Agent proxy account
> (search BOL for >xp_sqlagent_proxy_account<)?

> Also, consider that an NT account (whether an administrator or not) that
> has Access is Denied (at the share level or the file level), then NT will
> always deny that access (even if a member of administrators, who can
remove
> <g> their own access is denied).

> #srvrolemember  is a temporary table that will be automatically removed
> when the connection that created it is closed (i.e., you don't need to
> remove it, or, you can explicitly DROP TABLE #srvrolemember, but if the it
> temporary table is already dropped then that DROP will return an error
3701
> which is OK/expected).

> Thanks,

> Bill Hollinshead
> Microsoft, SQL Server

> This posting is provided "AS IS" with no warranties, and confers no
> rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

Bulk insert Error code 5(Access denied)

Post by Bill Hollinshead [MS » Sun, 27 Oct 2002 01:31:21


Hi Gerry,

Any chance that Everyone (at the file level, the folder level or the share
level) has Access is Denied?
Do you have the Guest account enabled? If so, you may want to disable it.
Any errors in the Security Event log?
Was this box hit with a worm/virus?
Make sure the SQL Server service startup account has permissions to
C:\TEMP: Log onto NT (at the SQL Server box) with that account (be sure to
check the Services' Logon tab and use that account when loggin on).
Try the whoami utility from xp_cmdshell.
Use xp_sqlagent_proxy_account to set an account that has already been
created in NT. Then log onto to NT using this new/set account and verify
that it too can access C:\temp.

I'm running out of ideas <g>. If these don't work then please respond and
I'll have to consult with NT.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

Bulk insert Error code 5(Access denied)

Post by Gerry Viato » Sun, 27 Oct 2002 03:34:59


Hi Bill thanks ,

  Everyone is not listed in there, we removed that as well. Only my
name(username) is in there to
         that shared Folder on the C drive of the SQL server2000 machine.
And Access is full.

Guest account is disabled
No errors that I see, just the Error in SQL Analyzer:

       exec bulkinsert
'MedGIreport','TempERCP','C:\HoldtextFiles\tmpercp.txt','|'

 Server: Msg 4861, Level 16, State 1, Line 2
Could not bulk insert because file 'C:\HoldtextFiles\tmpercp.txt' could not
be opened. Operating system error code 5(Access is denied.).

doing this local on SQL Server

No, on the virus/worm

I ran the whoami on the 2000Sql server
Returned the domain and my username

I used EXEC master.dbo.xp_sqlagent_proxy_account
 to add, still same results.

I rebooted and logged on to the Windows2000 machine that has SQLserver and
logged on with that username which is mine and through Windows Explorer
I can access the folder. Still get that error message above.

grrrrrrrr

Thanks Bill
Gerry



Quote:> Hi Gerry,

> Any chance that Everyone (at the file level, the folder level or the share
> level) has Access is Denied?
> Do you have the Guest account enabled? If so, you may want to disable it.
> Any errors in the Security Event log?
> Was this box hit with a worm/virus?
> Make sure the SQL Server service startup account has permissions to
> C:\TEMP: Log onto NT (at the SQL Server box) with that account (be sure to
> check the Services' Logon tab and use that account when loggin on).
> Try the whoami utility from xp_cmdshell.
> Use xp_sqlagent_proxy_account to set an account that has already been
> created in NT. Then log onto to NT using this new/set account and verify
> that it too can access C:\temp.

> I'm running out of ideas <g>. If these don't work then please respond and
> I'll have to consult with NT.

> Thanks,

> Bill Hollinshead
> Microsoft, SQL Server

> This posting is provided "AS IS" with no warranties, and confers no
> rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

Bulk insert Error code 5(Access denied)

Post by Bill Hollinshead [MS » Sun, 27 Oct 2002 07:40:57


Rats,

I don't think I mentioned the advanced rights in
http://msdn.microsoft.com/library/en-us/instsql/in_overview_6k1f.asp (see
the "Requirements for Domain User Account" section). Give your account all
the advanced rights and test.
Also use EM (server's Propeties|Security tab|Startup service account) to
change the startup account to System account and test.
You mentioned 'C:\HoldtextFiles\' in this post, and last post it was
'C:\TEMP
-- Is access denied no matter what folder is used?
-- Can you try a different drive and/or try a share?

NT had one idea:
Create a new domain account in a new global group. Add that new user to the
SQL Server box's local admins, (from me: ensure the advanced rights in the
above URL are set), startup SQL Server service with that new user, (from
me: try out xp_sqlagent_proxy_account again) and try the bulk insert again.
Add account to Domain Administrators and try again.

Baffled,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

Bulk insert Error code 5(Access denied)

Post by Bill Hollinshead [MS » Wed, 30 Oct 2002 01:19:19


Hi Gerry,

Make sure to restart the SQL Server service after applying rights (an NT
account will only get them when it logs on).
Could there be a Group Policy that is thwarting us? (This is part of the
reason for the test with a new domain account in a new domain group.)

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

Bulk insert Error code 5(Access denied)

Post by Gerry Viato » Wed, 30 Oct 2002 00:46:40


Hi Bill,

Thanks again

Gave all Rights.

I changed the startup account to System account(MSSQLSERVER Service)

I'm sorry about on the first post putting c:\Temp .  The actual
Folder(Shared Folder) is 'C:\HoldtextFiles\'

Correct, Access is Denied on any folder with  just my username under
Security tab.
again my username has rights to everything.  Or if I add another username
and give that user Administrators rights
(The same as mine) the same thing happens.

Trying the last part of your post, so far nothing.

Thanks again for your help

Gerry



Quote:> Rats,

> I don't think I mentioned the advanced rights in
> http://msdn.microsoft.com/library/en-us/instsql/in_overview_6k1f.asp (see
> the "Requirements for Domain User Account" section). Give your account all
> the advanced rights and test.
> Also use EM (server's Propeties|Security tab|Startup service account) to
> change the startup account to System account and test.
> You mentioned 'C:\HoldtextFiles\' in this post, and last post it was
> 'C:\TEMP
> -- Is access denied no matter what folder is used?
> -- Can you try a different drive and/or try a share?

> NT had one idea:
> Create a new domain account in a new global group. Add that new user to
the
> SQL Server box's local admins, (from me: ensure the advanced rights in the
> above URL are set), startup SQL Server service with that new user, (from
> me: try out xp_sqlagent_proxy_account again) and try the bulk insert
again.
> Add account to Domain Administrators and try again.

> Baffled,

> Bill Hollinshead
> Microsoft, SQL Server

> This posting is provided "AS IS" with no warranties, and confers no
> rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

Bulk insert Error code 5(Access denied)

Post by Gerry Viato » Wed, 30 Oct 2002 03:51:21


Hi Bill,

Working with someone here on the Group policy.  Will get back with you today
or
tomorrow on this.

Much appreciate your help.

Gerry



Quote:> Hi Gerry,

> Make sure to restart the SQL Server service after applying rights (an NT
> account will only get them when it logs on).
> Could there be a Group Policy that is thwarting us? (This is part of the
> reason for the test with a new domain account in a new domain group.)

> Thanks,

> Bill Hollinshead
> Microsoft, SQL Server

> This posting is provided "AS IS" with no warranties, and confers no
> rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

1. BULK INSERT Access Denied

I'm trying to do a BULK INSERT to the current database by
using Query Analyzer with the following SQL, SQL Server
2000, Standard Edition:

BULK INSERT tbl_BillFileLong
FROM '\\SERVER-C-NE-IL\D\Scrubbed.TXT'
WITH (FIELDTERMINATOR = '|')

The server is SERVER-C-NE-IL. The share \\SERVER-C-NE-IL\D
is mapped to the server's D: drive. I am logged on over
the network using NT security, I have full privileges on
the share, I am a member of the System Administrator's
role, and I am the dbo. Scrubbed.txt is present on the
share, and I can read and write the file over the network.

When I try to execute the SQL, I get this message:

Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file 'd:\Scrubbed.TXT' could
not be opened. Operating system error code 5(Access is
denied.).

I've tried changing the FROM parameter
to 'D:\Scrubbed.TXT', with the same results.

As I understand it, the FROM file needs to be accessible
from the server. Yet the server cannot seem to open it,
even though the file is physically on the server box.

Any insight would be appreciated.

Thanks,

Stu Bloom, MCSD

2. Next Meeting: Boston, June 5, 1995

3. Bulk Insert Access Denied

4. ADO Performance question

5. Error when bulk insert follows another large bulk insert

6. Connecting to another domain

7. bulk insert error: 'Operating system error code 5(Access is denied.)'

8. Legato changing time?

9. "Access Denied", error code 5 messages when trying to connect

10. Access denied, error code: -2147024891

11. Bulk Insert: soft coding "From" file

12. Code example for DTS Bulk Insert task

13. DTS Error running from Stored Procedure: Error opening datafile: Access denied