BULK INSERT "AccessDenied" on remote text file.

OK, Ive read many posts on this problem but have seen no resolution.

Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file.

Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head.

When anyone runs a Bulk Insert command that accesses a text file that is on a remote server, they get an "Access Denied". Now, I did a lot of testing and found that when the users executes the Bulk Insert command from the SQL Management studio on their desk top, they connect to the SQL box with their credentials (OK, that's correct right?), SQL then runs the Bulk Isert command which then reaches out to the remote file server but gets the "Access Denied". I check the logs and it shows that "Anonymous" was trying to accesss the file.

Why is Anonymouss coming over as credentials for SQL on a Bulk Insert? I'm no idiot but this sounds like a big crappy bug tha M$ will not fess to. I followed many suggestions, made sure NTFS and Share level permissions were correct (That was the first thing...), made sure the account that was running as SQL Server within the cluster on both nodes in the cluster was the same, that wasn't it, I even created a SPN for SQL to run and automatically register in AD with the credentials that SQL runs as. NOTHING!!!

Has anyone gotten their bulk insert to work when inserting from a file that is NOT local to the SQL box? Any help is appreciated, but putting the text files on the local SQL box IS NOT AN OPTION.

Thanks

What account is the sql server service running under? And when your devs connect, are they using windows accounts or SQL logins? Did you read topic "Importing Bulk Data by Using Bulk Insert or OPENROWSET" http://msdn2.microsoft.com/en-us/library/ms175915.aspx? See the "Security Considerations" section to understand how it works.

|||I am seeing the same thing. I am convinced it is a bug also.

It looks like SQL 2005 is not passing the proper windows login back to the other server, however it does locally. I too have tried everything recommended and have had no luck.|||According to section Security Considerations in the BOL topic I posted above, you should use impersonation/delegation to meet your needs. You guys saying you tried impersonation/delegation but it didn't work, is this correct? If so, post your repro below and we can take a look at it.|||

The account is a Domain account and we are using both Windows and SQL security. Upon reading the site you provided, the following state from the site indicates that the SQL service is the item accessing the remote file...

"BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process"

That criteria is met in that the SQL account used to run SQL does have FULL rioghts to the file from the share level to the NTFSlevel and has "Administrator" rights to both boxes. To further substantiate it's "Full" access, from any other application on the SQL box it can ope the text file.

Again it is because SQL is actually using "Anonymous" to log on to the server hosying the file.

Let me re0iterate this again. When SQL reaches out to access the text file for a bulk unsert, it get's the Access denied because it is using "Anonymous" to log on to the remote server. The SQL Service is running with domain credentials.

WHY IS SQL USING "ANONYMOUS"?

Sorry for the frustrations here but it looks like a lot of people are having this problem and no one is giving a sulution.

|||

Yes, we set delegation on Both nodes of the SQL cluster and the server hosting the files as well as the Domain account tha the SQL instance runs as. We also have a SPN running for the instance of SQL for the domain account SQL runs as.

Now, what is a "repro" and is delegation the same thing as imersonation?

Thank you your help.

|||

Sorry, repro is short for "reproduce". Basically give me an example that I can try to duplicate on my machines here. If we can't figure out the problem over the forum, we can try your scenario in our lab to experience first hand what you're doing, but it can only be done if you give us the exact steps. It's the easiest way for us to see and understand the problem you're facing.

|||

Delegation is a Windows things. If you want to use Bulk Insert via 2-hop scenario (Person on Computer1 connects to Computer2 which reads file from Computer3 - this looks like what you're describing), you have to enable Windows delegation. If you open Windows Help and search for Delegation, you should come across a topic "Delegation authentication: Security". I believe web scenarios who need to validate with user credentials and have the IIS server on a different box than their SQL Server have to do the same thing.

Cos_SQL, your quote is for a standard 1-hop scenario, but you have a 2-hop scenario. I am guessing this is why you see "Anonymous" access, and why you're probably getting access denied.

The above is what I gathered from reading BOL and the bugs database, so let me know if you hit any snags or are still hitting problems.

|||

OK, here's our setup...

Active directory Account: SQL05svc (Administrator to all servers in question.)
SQL 2005 Cluster: 2 Node Active Passive (SQLNodeA & SQLNodeB)
File Server: Fsrv01 (Windows 2003 Server - Fully Patched)
Client WS: Client01 (Windows XP - Fully Patched)

The SQL CLuster runs SQL, SSIS and Reporting Service and runs on the domain account: SQL05svc

Security is set on the Windows file share for "Full" rights to the share as well as the NTFS level for the domain account SQL05svc.

From one of the SQL 2005 server nodes that has all resources assigned to it we log onto it and run a query from QUERY analyzer...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

It runs fine. One hop.
Check the Security Log of the Fsrv01 and it should show that "SQL05svc" acessed the server.

From the clients PC, Client01, we connect to the SQL instance "VirtualServer\SQLInstanceName" via SQL 2005's "Microsoft SQL Server Management Studio", open a Query Analyzer session and run the same query...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

This is a two hop scenario.
And we get the following error...

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\Fsrv01\Data\YanTest\CS_TEST.txt" could not be opened. Operating system error code 5(Access is denied.).

Now check the security logs in Fsrv01 "SQL05svc" and you will see that "Anonymous" tried to get to Fsrv01.

It's a pretty vanilla setup.

Please let me know your findings.

Again, please remember we have delegation configured for the two SQL nodes, the Fsrv01, and the domain account SQL05svc.
We also have a SPN for the instance of SQL in the Domain for SQL05svc.

Here's a funny part, if you move ALL the resources from the SQL node you are logged into and try the same query, you get the same error. This is because you have now introduced your setup as a two hop scenario. The resources are no longer on the local server!

Thanks again for trying to help us.

|||

I have seen one post where someone changed their process to use osql and said it fised their problem. I have tried it and we get the error "Login Failed for user: blahblahblah."

Does anyone have this working?

|||I have not had time today to replicate the problem. But what you describe is exactly what I am saw in our environment (without the cluster).

The thing that worked for me was to move the share to the SQL server (which was easy for my environment). The only other thing that worked was go give "Everyone" read rights to the share, not an option in my situation. My share was set to only the AD User with "Full Access".

Even as a "Domain Admin" with full rights to everything, trying to run the query from a client pc to the SQL server, accessing a file on a different file server, gives me "Access Denied" error. Running 2005 (9.0.2153 32bit) on 2003 R2 on both servers.|||

Moving the files local to the SQL server will work but in a clustered environment, if the resources fail over to the other node or if the admin decides to move the resources then your back to the 2 hop scenario.

Thanks for your reply though.

|||

In fact, I have the same problem since I migrated from a windows 2k server to a windows 2003 server.

We have sql 2000 running on it : it doesn' function.

I tried on sql 2000 running on windows 2003 and it fails : I put all rights to everybody, I changed from local system count to administrator to launch sql server and no change : it only works if sql is running on windows 2000 ?

|||Issue is resolved in SP2 beta.|||

Issue WAS resolved in SP2 CTP (Beta).

BUT it is not resolved in SP2 final release (the same problem as before).

BULK INSERT "AccessDenied" on remote text file.

OK, Ive read many posts on this problem but have seen no resolution.

Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file.

Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head.

When anyone runs a Bulk Insert command that accesses a text file that is on a remote server, they get an "Access Denied". Now, I did a lot of testing and found that when the users executes the Bulk Insert command from the SQL Management studio on their desk top, they connect to the SQL box with their credentials (OK, that's correct right?), SQL then runs the Bulk Isert command which then reaches out to the remote file server but gets the "Access Denied". I check the logs and it shows that "Anonymous" was trying to accesss the file.

Why is Anonymouss coming over as credentials for SQL on a Bulk Insert? I'm no idiot but this sounds like a big crappy bug tha M$ will not fess to. I followed many suggestions, made sure NTFS and Share level permissions were correct (That was the first thing...), made sure the account that was running as SQL Server within the cluster on both nodes in the cluster was the same, that wasn't it, I even created a SPN for SQL to run and automatically register in AD with the credentials that SQL runs as. NOTHING!!!

Has anyone gotten their bulk insert to work when inserting from a file that is NOT local to the SQL box? Any help is appreciated, but putting the text files on the local SQL box IS NOT AN OPTION.

Thanks

What account is the sql server service running under? And when your devs connect, are they using windows accounts or SQL logins? Did you read topic "Importing Bulk Data by Using Bulk Insert or OPENROWSET" http://msdn2.microsoft.com/en-us/library/ms175915.aspx? See the "Security Considerations" section to understand how it works.

|||I am seeing the same thing. I am convinced it is a bug also.

It looks like SQL 2005 is not passing the proper windows login back to the other server, however it does locally. I too have tried everything recommended and have had no luck.|||According to section Security Considerations in the BOL topic I posted above, you should use impersonation/delegation to meet your needs. You guys saying you tried impersonation/delegation but it didn't work, is this correct? If so, post your repro below and we can take a look at it.|||

The account is a Domain account and we are using both Windows and SQL security. Upon reading the site you provided, the following state from the site indicates that the SQL service is the item accessing the remote file...

"BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process"

That criteria is met in that the SQL account used to run SQL does have FULL rioghts to the file from the share level to the NTFSlevel and has "Administrator" rights to both boxes. To further substantiate it's "Full" access, from any other application on the SQL box it can ope the text file.

Again it is because SQL is actually using "Anonymous" to log on to the server hosying the file.

Let me re0iterate this again. When SQL reaches out to access the text file for a bulk unsert, it get's the Access denied because it is using "Anonymous" to log on to the remote server. The SQL Service is running with domain credentials.

WHY IS SQL USING "ANONYMOUS"?

Sorry for the frustrations here but it looks like a lot of people are having this problem and no one is giving a sulution.

|||

Yes, we set delegation on Both nodes of the SQL cluster and the server hosting the files as well as the Domain account tha the SQL instance runs as. We also have a SPN running for the instance of SQL for the domain account SQL runs as.

Now, what is a "repro" and is delegation the same thing as imersonation?

Thank you your help.

|||

Sorry, repro is short for "reproduce". Basically give me an example that I can try to duplicate on my machines here. If we can't figure out the problem over the forum, we can try your scenario in our lab to experience first hand what you're doing, but it can only be done if you give us the exact steps. It's the easiest way for us to see and understand the problem you're facing.

|||

Delegation is a Windows things. If you want to use Bulk Insert via 2-hop scenario (Person on Computer1 connects to Computer2 which reads file from Computer3 - this looks like what you're describing), you have to enable Windows delegation. If you open Windows Help and search for Delegation, you should come across a topic "Delegation authentication: Security". I believe web scenarios who need to validate with user credentials and have the IIS server on a different box than their SQL Server have to do the same thing.

Cos_SQL, your quote is for a standard 1-hop scenario, but you have a 2-hop scenario. I am guessing this is why you see "Anonymous" access, and why you're probably getting access denied.

The above is what I gathered from reading BOL and the bugs database, so let me know if you hit any snags or are still hitting problems.

|||

OK, here's our setup...

Active directory Account: SQL05svc (Administrator to all servers in question.)
SQL 2005 Cluster: 2 Node Active Passive (SQLNodeA & SQLNodeB)
File Server: Fsrv01 (Windows 2003 Server - Fully Patched)
Client WS: Client01 (Windows XP - Fully Patched)

The SQL CLuster runs SQL, SSIS and Reporting Service and runs on the domain account: SQL05svc

Security is set on the Windows file share for "Full" rights to the share as well as the NTFS level for the domain account SQL05svc.

From one of the SQL 2005 server nodes that has all resources assigned to it we log onto it and run a query from QUERY analyzer...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

It runs fine. One hop.
Check the Security Log of the Fsrv01 and it should show that "SQL05svc" acessed the server.

From the clients PC, Client01, we connect to the SQL instance "VirtualServer\SQLInstanceName" via SQL 2005's "Microsoft SQL Server Management Studio", open a Query Analyzer session and run the same query...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

This is a two hop scenario.
And we get the following error...

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\Fsrv01\Data\YanTest\CS_TEST.txt" could not be opened. Operating system error code 5(Access is denied.).

Now check the security logs in Fsrv01 "SQL05svc" and you will see that "Anonymous" tried to get to Fsrv01.

It's a pretty vanilla setup.

Please let me know your findings.

Again, please remember we have delegation configured for the two SQL nodes, the Fsrv01, and the domain account SQL05svc.
We also have a SPN for the instance of SQL in the Domain for SQL05svc.

Here's a funny part, if you move ALL the resources from the SQL node you are logged into and try the same query, you get the same error. This is because you have now introduced your setup as a two hop scenario. The resources are no longer on the local server!

Thanks again for trying to help us.

|||

I have seen one post where someone changed their process to use osql and said it fised their problem. I have tried it and we get the error "Login Failed for user: blahblahblah."

Does anyone have this working?

|||I have not had time today to replicate the problem. But what you describe is exactly what I am saw in our environment (without the cluster).

The thing that worked for me was to move the share to the SQL server (which was easy for my environment). The only other thing that worked was go give "Everyone" read rights to the share, not an option in my situation. My share was set to only the AD User with "Full Access".

Even as a "Domain Admin" with full rights to everything, trying to run the query from a client pc to the SQL server, accessing a file on a different file server, gives me "Access Denied" error. Running 2005 (9.0.2153 32bit) on 2003 R2 on both servers.|||

Moving the files local to the SQL server will work but in a clustered environment, if the resources fail over to the other node or if the admin decides to move the resources then your back to the 2 hop scenario.

Thanks for your reply though.

|||

In fact, I have the same problem since I migrated from a windows 2k server to a windows 2003 server.

We have sql 2000 running on it : it doesn' function.

I tried on sql 2000 running on windows 2003 and it fails : I put all rights to everybody, I changed from local system count to administrator to launch sql server and no change : it only works if sql is running on windows 2000 ?

|||Issue is resolved in SP2 beta.|||

Issue WAS resolved in SP2 CTP (Beta).

BUT it is not resolved in SP2 final release (the same problem as before).

BULK INSERT "AccessDenied" on remote text file.

OK, Ive read many posts on this problem but have seen no resolution.

Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file.

Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head.

When anyone runs a Bulk Insert command that accesses a text file that is on a remote server, they get an "Access Denied". Now, I did a lot of testing and found that when the users executes the Bulk Insert command from the SQL Management studio on their desk top, they connect to the SQL box with their credentials (OK, that's correct right?), SQL then runs the Bulk Isert command which then reaches out to the remote file server but gets the "Access Denied". I check the logs and it shows that "Anonymous" was trying to accesss the file.

Why is Anonymouss coming over as credentials for SQL on a Bulk Insert? I'm no idiot but this sounds like a big crappy bug tha M$ will not fess to. I followed many suggestions, made sure NTFS and Share level permissions were correct (That was the first thing...), made sure the account that was running as SQL Server within the cluster on both nodes in the cluster was the same, that wasn't it, I even created a SPN for SQL to run and automatically register in AD with the credentials that SQL runs as. NOTHING!!!

Has anyone gotten their bulk insert to work when inserting from a file that is NOT local to the SQL box? Any help is appreciated, but putting the text files on the local SQL box IS NOT AN OPTION.

Thanks

What account is the sql server service running under? And when your devs connect, are they using windows accounts or SQL logins? Did you read topic "Importing Bulk Data by Using Bulk Insert or OPENROWSET" http://msdn2.microsoft.com/en-us/library/ms175915.aspx? See the "Security Considerations" section to understand how it works.

|||I am seeing the same thing. I am convinced it is a bug also.

It looks like SQL 2005 is not passing the proper windows login back to the other server, however it does locally. I too have tried everything recommended and have had no luck.|||According to section Security Considerations in the BOL topic I posted above, you should use impersonation/delegation to meet your needs. You guys saying you tried impersonation/delegation but it didn't work, is this correct? If so, post your repro below and we can take a look at it.|||

The account is a Domain account and we are using both Windows and SQL security. Upon reading the site you provided, the following state from the site indicates that the SQL service is the item accessing the remote file...

"BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process"

That criteria is met in that the SQL account used to run SQL does have FULL rioghts to the file from the share level to the NTFSlevel and has "Administrator" rights to both boxes. To further substantiate it's "Full" access, from any other application on the SQL box it can ope the text file.

Again it is because SQL is actually using "Anonymous" to log on to the server hosying the file.

Let me re0iterate this again. When SQL reaches out to access the text file for a bulk unsert, it get's the Access denied because it is using "Anonymous" to log on to the remote server. The SQL Service is running with domain credentials.

WHY IS SQL USING "ANONYMOUS"?

Sorry for the frustrations here but it looks like a lot of people are having this problem and no one is giving a sulution.

|||

Yes, we set delegation on Both nodes of the SQL cluster and the server hosting the files as well as the Domain account tha the SQL instance runs as. We also have a SPN running for the instance of SQL for the domain account SQL runs as.

Now, what is a "repro" and is delegation the same thing as imersonation?

Thank you your help.

|||

Sorry, repro is short for "reproduce". Basically give me an example that I can try to duplicate on my machines here. If we can't figure out the problem over the forum, we can try your scenario in our lab to experience first hand what you're doing, but it can only be done if you give us the exact steps. It's the easiest way for us to see and understand the problem you're facing.

|||

Delegation is a Windows things. If you want to use Bulk Insert via 2-hop scenario (Person on Computer1 connects to Computer2 which reads file from Computer3 - this looks like what you're describing), you have to enable Windows delegation. If you open Windows Help and search for Delegation, you should come across a topic "Delegation authentication: Security". I believe web scenarios who need to validate with user credentials and have the IIS server on a different box than their SQL Server have to do the same thing.

Cos_SQL, your quote is for a standard 1-hop scenario, but you have a 2-hop scenario. I am guessing this is why you see "Anonymous" access, and why you're probably getting access denied.

The above is what I gathered from reading BOL and the bugs database, so let me know if you hit any snags or are still hitting problems.

|||

OK, here's our setup...

Active directory Account: SQL05svc (Administrator to all servers in question.)
SQL 2005 Cluster: 2 Node Active Passive (SQLNodeA & SQLNodeB)
File Server: Fsrv01 (Windows 2003 Server - Fully Patched)
Client WS: Client01 (Windows XP - Fully Patched)

The SQL CLuster runs SQL, SSIS and Reporting Service and runs on the domain account: SQL05svc

Security is set on the Windows file share for "Full" rights to the share as well as the NTFS level for the domain account SQL05svc.

From one of the SQL 2005 server nodes that has all resources assigned to it we log onto it and run a query from QUERY analyzer...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

It runs fine. One hop.
Check the Security Log of the Fsrv01 and it should show that "SQL05svc" acessed the server.

From the clients PC, Client01, we connect to the SQL instance "VirtualServer\SQLInstanceName" via SQL 2005's "Microsoft SQL Server Management Studio", open a Query Analyzer session and run the same query...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

This is a two hop scenario.
And we get the following error...

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\Fsrv01\Data\YanTest\CS_TEST.txt" could not be opened. Operating system error code 5(Access is denied.).

Now check the security logs in Fsrv01 "SQL05svc" and you will see that "Anonymous" tried to get to Fsrv01.

It's a pretty vanilla setup.

Please let me know your findings.

Again, please remember we have delegation configured for the two SQL nodes, the Fsrv01, and the domain account SQL05svc.
We also have a SPN for the instance of SQL in the Domain for SQL05svc.

Here's a funny part, if you move ALL the resources from the SQL node you are logged into and try the same query, you get the same error. This is because you have now introduced your setup as a two hop scenario. The resources are no longer on the local server!

Thanks again for trying to help us.

|||

I have seen one post where someone changed their process to use osql and said it fised their problem. I have tried it and we get the error "Login Failed for user: blahblahblah."

Does anyone have this working?

|||I have not had time today to replicate the problem. But what you describe is exactly what I am saw in our environment (without the cluster).

The thing that worked for me was to move the share to the SQL server (which was easy for my environment). The only other thing that worked was go give "Everyone" read rights to the share, not an option in my situation. My share was set to only the AD User with "Full Access".

Even as a "Domain Admin" with full rights to everything, trying to run the query from a client pc to the SQL server, accessing a file on a different file server, gives me "Access Denied" error. Running 2005 (9.0.2153 32bit) on 2003 R2 on both servers.|||

Moving the files local to the SQL server will work but in a clustered environment, if the resources fail over to the other node or if the admin decides to move the resources then your back to the 2 hop scenario.

Thanks for your reply though.

|||

In fact, I have the same problem since I migrated from a windows 2k server to a windows 2003 server.

We have sql 2000 running on it : it doesn' function.

I tried on sql 2000 running on windows 2003 and it fails : I put all rights to everybody, I changed from local system count to administrator to launch sql server and no change : it only works if sql is running on windows 2000 ?

|||Issue is resolved in SP2 beta.|||

Issue WAS resolved in SP2 CTP (Beta).

BUT it is not resolved in SP2 final release (the same problem as before).

BULK INSERT "AccessDenied" on remote text file.

OK, Ive read many posts on this problem but have seen no resolution.

Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file.

Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head.

When anyone runs a Bulk Insert command that accesses a text file that is on a remote server, they get an "Access Denied". Now, I did a lot of testing and found that when the users executes the Bulk Insert command from the SQL Management studio on their desk top, they connect to the SQL box with their credentials (OK, that's correct right?), SQL then runs the Bulk Isert command which then reaches out to the remote file server but gets the "Access Denied". I check the logs and it shows that "Anonymous" was trying to accesss the file.

Why is Anonymouss coming over as credentials for SQL on a Bulk Insert? I'm no idiot but this sounds like a big crappy bug tha M$ will not fess to. I followed many suggestions, made sure NTFS and Share level permissions were correct (That was the first thing...), made sure the account that was running as SQL Server within the cluster on both nodes in the cluster was the same, that wasn't it, I even created a SPN for SQL to run and automatically register in AD with the credentials that SQL runs as. NOTHING!!!

Has anyone gotten their bulk insert to work when inserting from a file that is NOT local to the SQL box? Any help is appreciated, but putting the text files on the local SQL box IS NOT AN OPTION.

Thanks

What account is the sql server service running under? And when your devs connect, are they using windows accounts or SQL logins? Did you read topic "Importing Bulk Data by Using Bulk Insert or OPENROWSET" http://msdn2.microsoft.com/en-us/library/ms175915.aspx? See the "Security Considerations" section to understand how it works.

|||I am seeing the same thing. I am convinced it is a bug also.

It looks like SQL 2005 is not passing the proper windows login back to the other server, however it does locally. I too have tried everything recommended and have had no luck.

|||According to section Security Considerations in the BOL topic I posted above, you should use impersonation/delegation to meet your needs. You guys saying you tried impersonation/delegation but it didn't work, is this correct? If so, post your repro below and we can take a look at it.|||

The account is a Domain account and we are using both Windows and SQL security. Upon reading the site you provided, the following state from the site indicates that the SQL service is the item accessing the remote file...

"BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process"

That criteria is met in that the SQL account used to run SQL does have FULL rioghts to the file from the share level to the NTFSlevel and has "Administrator" rights to both boxes. To further substantiate it's "Full" access, from any other application on the SQL box it can ope the text file.

Again it is because SQL is actually using "Anonymous" to log on to the server hosying the file.

Let me re0iterate this again. When SQL reaches out to access the text file for a bulk unsert, it get's the Access denied because it is using "Anonymous" to log on to the remote server. The SQL Service is running with domain credentials.

WHY IS SQL USING "ANONYMOUS"?

Sorry for the frustrations here but it looks like a lot of people are having this problem and no one is giving a sulution.

|||

Yes, we set delegation on Both nodes of the SQL cluster and the server hosting the files as well as the Domain account tha the SQL instance runs as. We also have a SPN running for the instance of SQL for the domain account SQL runs as.

Now, what is a "repro" and is delegation the same thing as imersonation?

Thank you your help.

|||

Sorry, repro is short for "reproduce". Basically give me an example that I can try to duplicate on my machines here. If we can't figure out the problem over the forum, we can try your scenario in our lab to experience first hand what you're doing, but it can only be done if you give us the exact steps. It's the easiest way for us to see and understand the problem you're facing.

|||

Delegation is a Windows things. If you want to use Bulk Insert via 2-hop scenario (Person on Computer1 connects to Computer2 which reads file from Computer3 - this looks like what you're describing), you have to enable Windows delegation. If you open Windows Help and search for Delegation, you should come across a topic "Delegation authentication: Security". I believe web scenarios who need to validate with user credentials and have the IIS server on a different box than their SQL Server have to do the same thing.

Cos_SQL, your quote is for a standard 1-hop scenario, but you have a 2-hop scenario. I am guessing this is why you see "Anonymous" access, and why you're probably getting access denied.

The above is what I gathered from reading BOL and the bugs database, so let me know if you hit any snags or are still hitting problems.

|||

OK, here's our setup...

Active directory Account: SQL05svc (Administrator to all servers in question.)
SQL 2005 Cluster: 2 Node Active Passive (SQLNodeA & SQLNodeB)
File Server: Fsrv01 (Windows 2003 Server - Fully Patched)
Client WS: Client01 (Windows XP - Fully Patched)

The SQL CLuster runs SQL, SSIS and Reporting Service and runs on the domain account: SQL05svc

Security is set on the Windows file share for "Full" rights to the share as well as the NTFS level for the domain account SQL05svc.

From one of the SQL 2005 server nodes that has all resources assigned to it we log onto it and run a query from QUERY analyzer...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

It runs fine. One hop.
Check the Security Log of the Fsrv01 and it should show that "SQL05svc" acessed the server.

From the clients PC, Client01, we connect to the SQL instance "VirtualServer\SQLInstanceName" via SQL 2005's "Microsoft SQL Server Management Studio", open a Query Analyzer session and run the same query...

BULK INSERT dbo.CS_Test FROM '\\Fsrv01\Data\YanTest\CS_TEST.txt'
WITH (FIELDTERMINATOR = '","',MAXERRORS=0,TABLOCK,BATCHSIZE=300000,
ERRORFILE='\\Fsrv01\Data\YanTest\CS_TEST_Err.txt',
ROWTERMINATOR ='\"\n\"',
DATAFILETYPE = 'char',
CODEPAGE='1252')
GO

This is a two hop scenario.
And we get the following error...

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\Fsrv01\Data\YanTest\CS_TEST.txt" could not be opened. Operating system error code 5(Access is denied.).

Now check the security logs in Fsrv01 "SQL05svc" and you will see that "Anonymous" tried to get to Fsrv01.

It's a pretty vanilla setup.

Please let me know your findings.

Again, please remember we have delegation configured for the two SQL nodes, the Fsrv01, and the domain account SQL05svc.
We also have a SPN for the instance of SQL in the Domain for SQL05svc.

Here's a funny part, if you move ALL the resources from the SQL node you are logged into and try the same query, you get the same error. This is because you have now introduced your setup as a two hop scenario. The resources are no longer on the local server!

Thanks again for trying to help us.

|||

I have seen one post where someone changed their process to use osql and said it fised their problem. I have tried it and we get the error "Login Failed for user: blahblahblah."

Does anyone have this working?

|||I have not had time today to replicate the problem. But what you describe is exactly what I am saw in our environment (without the cluster).

The thing that worked for me was to move the share to the SQL server (which was easy for my environment). The only other thing that worked was go give "Everyone" read rights to the share, not an option in my situation. My share was set to only the AD User with "Full Access".

Even as a "Domain Admin" with full rights to everything, trying to run the query from a client pc to the SQL server, accessing a file on a different file server, gives me "Access Denied" error. Running 2005 (9.0.2153 32bit) on 2003 R2 on both servers.

|||

Moving the files local to the SQL server will work but in a clustered environment, if the resources fail over to the other node or if the admin decides to move the resources then your back to the 2 hop scenario.

Thanks for your reply though.

|||

In fact, I have the same problem since I migrated from a windows 2k server to a windows 2003 server.

We have sql 2000 running on it : it doesn' function.

I tried on sql 2000 running on windows 2003 and it fails : I put all rights to everybody, I changed from local system count to administrator to launch sql server and no change : it only works if sql is running on windows 2000 ?

|||Issue is resolved in SP2 beta.
|||

Issue WAS resolved in SP2 CTP (Beta).

BUT it is not resolved in SP2 final release (the same problem as before).

builtin/administrators

Hi, our sql box has too many people (including sql service account) with
local admin rights, some even have domain admin rights. I am trying to
tighten the security on those boxes and decrease the security level of those
people. What I am planning to do is take builtin/administrator login out of
the sql box, and add sql services account back and grant SA rights to it.
Does anyone see any problem with this approach? Thanks.
I suggest you start with creating a new windows group, ad appropriate windows users, add it to SQL
Server and grant sysadmin for that. Unless you already have such, of course. :-)
One thing I know can be a problem is full-text search. I think that "local system" is granted login
to SQL server through builtin\Administrators, and hereby lies the problem. That should be OK by
adding "NT Authority" (or whatever the physical name for LocalSystem is). I suggest you Google the
archives for a bit more info on that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"flologic" <flo@.flo.net> wrote in message news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of those
> people. What I am planning to do is take builtin/administrator login out of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>
|||flo,
Here is an article (talking about clusters) that addresses some of your
concerns.
http://support.microsoft.com/default...b;en-us;263712
One example of a side-effect that you must manage is:
http://support.microsoft.com/default...b;en-us;237604
Also, from the BOL on: Setting up Windows Services Accounts
If the startup account assigned to the MSSQLServer Service is not a member
of the Local Administrators group, or if the BUILTIN\Administrators SQL
Server login has been removed, you must add the startup account for the
MSSQLServer service or the SQLServerAgent service, or both, to the SQL
Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
user a logon to SQL Server.
Hope that helps you.
Russell Fields
"flologic" <flo@.flo.net> wrote in message
news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of
those
> people. What I am planning to do is take builtin/administrator login out
of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>
|||The account for local system is NT Authority\SYSTEM.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||If you add ANY NT group and grant that group SQL Admin privileges then you
can NOT prevent the NT admins from coming in... All they have to do is add
themselves to the NT group;...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:Of5AOPGhEHA.1356@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> flo,
> Here is an article (talking about clusters) that addresses some of your
> concerns.
> http://support.microsoft.com/default...b;en-us;263712
> One example of a side-effect that you must manage is:
> http://support.microsoft.com/default...b;en-us;237604
> Also, from the BOL on: Setting up Windows Services Accounts
> If the startup account assigned to the MSSQLServer Service is not a member
> of the Local Administrators group, or if the BUILTIN\Administrators SQL
> Server login has been removed, you must add the startup account for the
> MSSQLServer service or the SQLServerAgent service, or both, to the SQL
> Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
> user a logon to SQL Server.
> Hope that helps you.
> Russell Fields
> "flologic" <flo@.flo.net> wrote in message
> news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> those
> of
it.
>

builtin/administrators

Hi, our sql box has too many people (including sql service account) with
local admin rights, some even have domain admin rights. I am trying to
tighten the security on those boxes and decrease the security level of those
people. What I am planning to do is take builtin/administrator login out of
the sql box, and add sql services account back and grant SA rights to it.
Does anyone see any problem with this approach? Thanks.I suggest you start with creating a new windows group, ad appropriate windows users, add it to SQL
Server and grant sysadmin for that. Unless you already have such, of course. :-)
One thing I know can be a problem is full-text search. I think that "local system" is granted login
to SQL server through builtin\Administrators, and hereby lies the problem. That should be OK by
adding "NT Authority" (or whatever the physical name for LocalSystem is). I suggest you Google the
archives for a bit more info on that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"flologic" <flo@.flo.net> wrote in message news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of those
> people. What I am planning to do is take builtin/administrator login out of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>|||flo,
Here is an article (talking about clusters) that addresses some of your
concerns.
http://support.microsoft.com/default.aspx?scid=kb;en-us;263712
One example of a side-effect that you must manage is:
http://support.microsoft.com/default.aspx?scid=kb;en-us;237604
Also, from the BOL on: Setting up Windows Services Accounts
If the startup account assigned to the MSSQLServer Service is not a member
of the Local Administrators group, or if the BUILTIN\Administrators SQL
Server login has been removed, you must add the startup account for the
MSSQLServer service or the SQLServerAgent service, or both, to the SQL
Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
user a logon to SQL Server.
Hope that helps you.
Russell Fields
"flologic" <flo@.flo.net> wrote in message
news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of
those
> people. What I am planning to do is take builtin/administrator login out
of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>|||The account for local system is NT Authority\SYSTEM.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||If you add ANY NT group and grant that group SQL Admin privileges then you
can NOT prevent the NT admins from coming in... All they have to do is add
themselves to the NT group;...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:Of5AOPGhEHA.1356@.TK2MSFTNGP09.phx.gbl...
> flo,
> Here is an article (talking about clusters) that addresses some of your
> concerns.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;263712
> One example of a side-effect that you must manage is:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;237604
> Also, from the BOL on: Setting up Windows Services Accounts
> If the startup account assigned to the MSSQLServer Service is not a member
> of the Local Administrators group, or if the BUILTIN\Administrators SQL
> Server login has been removed, you must add the startup account for the
> MSSQLServer service or the SQLServerAgent service, or both, to the SQL
> Server system administrators (sysadmin) role. Grant the [Domain\NTaccount]
> user a logon to SQL Server.
> Hope that helps you.
> Russell Fields
> "flologic" <flo@.flo.net> wrote in message
> news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> > Hi, our sql box has too many people (including sql service account) with
> > local admin rights, some even have domain admin rights. I am trying to
> > tighten the security on those boxes and decrease the security level of
> those
> > people. What I am planning to do is take builtin/administrator login out
> of
> > the sql box, and add sql services account back and grant SA rights to
it.
> > Does anyone see any problem with this approach? Thanks.
> >
> >
>

builtin/administrators

Hi, our sql box has too many people (including sql service account) with
local admin rights, some even have domain admin rights. I am trying to
tighten the security on those boxes and decrease the security level of those
people. What I am planning to do is take builtin/administrator login out of
the sql box, and add sql services account back and grant SA rights to it.
Does anyone see any problem with this approach? Thanks.I suggest you start with creating a new windows group, ad appropriate window
s users, add it to SQL
Server and grant sysadmin for that. Unless you already have such, of course.
:-)
One thing I know can be a problem is full-text search. I think that "local s
ystem" is granted login
to SQL server through builtin\Administrators, and hereby lies the problem. T
hat should be OK by
adding "NT Authority" (or whatever the physical name for LocalSystem is). I
suggest you Google the
archives for a bit more info on that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"flologic" <flo@.flo.net> wrote in message news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...[vbcol
=seagreen]
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of tho
se
> people. What I am planning to do is take builtin/administrator login out o
f
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>[/vbcol]|||flo,
Here is an article (talking about clusters) that addresses some of your
concerns.
http://support.microsoft.com/defaul...kb;en-us;263712
One example of a side-effect that you must manage is:
http://support.microsoft.com/defaul...kb;en-us;237604
Also, from the BOL on: Setting up Windows Services Accounts
If the startup account assigned to the MSSQLServer Service is not a member
of the Local Administrators group, or if the BUILTIN\Administrators SQL
Server login has been removed, you must add the startup account for the
MSSQLServer service or the SQLServerAgent service, or both, to the SQL
Server system administrators (sysadmin) role. Grant the [Domain\NTaccoun
t]
user a logon to SQL Server.
Hope that helps you.
Russell Fields
"flologic" <flo@.flo.net> wrote in message
news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Hi, our sql box has too many people (including sql service account) with
> local admin rights, some even have domain admin rights. I am trying to
> tighten the security on those boxes and decrease the security level of
those
> people. What I am planning to do is take builtin/administrator login out
of
> the sql box, and add sql services account back and grant SA rights to it.
> Does anyone see any problem with this approach? Thanks.
>|||The account for local system is NT Authority\SYSTEM.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||If you add ANY NT group and grant that group SQL Admin privileges then you
can NOT prevent the NT admins from coming in... All they have to do is add
themselves to the NT group;...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:Of5AOPGhEHA.1356@.TK2MSFTNGP09.phx.gbl...
> flo,
> Here is an article (talking about clusters) that addresses some of your
> concerns.
> http://support.microsoft.com/defaul...kb;en-us;263712
> One example of a side-effect that you must manage is:
> http://support.microsoft.com/defaul...kb;en-us;237604
> Also, from the BOL on: Setting up Windows Services Accounts
> If the startup account assigned to the MSSQLServer Service is not a member
> of the Local Administrators group, or if the BUILTIN\Administrators SQL
> Server login has been removed, you must add the startup account for the
> MSSQLServer service or the SQLServerAgent service, or both, to the SQL
> Server system administrators (sysadmin) role. Grant the [Domain\NTacco
unt]
> user a logon to SQL Server.
> Hope that helps you.
> Russell Fields
> "flologic" <flo@.flo.net> wrote in message
> news:eReee6FhEHA.632@.TK2MSFTNGP12.phx.gbl...
> those
> of
it.[vbcol=seagreen]
>

Building Development Box for 2005

I'm planning on building a new desktop box for developing apps with VS 2005
and SQL Server 2005.
Would it make sense to have two SATA drives, say C: and D: and install SQL
Server on D?
What would you recommend would be a reasonably good configuration - RAM,
CPU, video, HDs, etc considering a budget of about $2000, NOT $5000.
Thanks.You should be able to buy something cheaper than $2000, i would think.
Not much video is necessary so pretty much anything on board would do
the job.
A gig of ram - or more.
Any old fairly fast HD would do...
Plus an external drive to back up to.
You might aklso want to think about raid so you minimise your risk of
losing anything.
Only having the one cevelopment PC is a risk if you're working at home
or in an office where you can't use someone else's PC.
I'll probably be buying a 2nd (cheap) pc for my home office.
You can then back up from one to the other and if you mess one up the
other is still ok.
Oh.
And a router firewall. These are cheap now.

Build Query button in DTS 2000 Package Designer

When trying to modify a DTS package in DTS 2000 Package Designer in SQL Server 2005, the Build Query button on a Transform SQL Data Task box is not enabled.

Does anyone know why?

If its not a complex package then why don't you try build in SSIS.