Import data to Sql 2005 from a remote PC

Question

Import data to Sql 2005 from a remote PC

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

tmacdonald SSC Veteran Points: 215 More actions · Answer 1

Steve,

Sorry about the copy and paste, I didn't relalize there was a qoute button. Like I stated in my first post, I'm attempting to run this from a vb.net application. I just attempted to run this from the SSMS it doesn't work either. I know I have given all the appropriate permissions to the folder. This is very frustrating.

Terry

Pavel Pawlowski Ten Centuries Points: 1244 More actions · Answer 2

If you are able to login to the SQL server machine under the account under which the instance is running, then try to login and try access the files on remove machine.

If you are able to access them from within explorer, then you are making something wrong when specifying path or so in SSMS. If you are not able to access them, then its problem of rights or shared folder setting on the remote pc.

Please note, that you have to specify the path to the files like you were on the SQL server machine and doesn't matter if you specify it in SSMS or running SSIS to transform and input the data.

tmacdonald SSC Veteran Points: 215 More actions · Answer 3

Pavel Pawlowski (12/10/2009)
If you are able to login to the SQL server machine under the account under which the instance is running, then try to login and try access the files on remove machine.

Unfortunatly I have tried this, I can log into the sql server and the remote machine with the same user name. I have no problem doing this. However I am still unable to get the sql server PC to access the folder on the Application PC.

Terry

Pavel Pawlowski Ten Centuries Points: 1244 More actions · Answer 4

tmacdonald (12/10/2009)
Pavel Pawlowski (12/10/2009)
If you are able to login to the SQL server machine under the account under which the instance is running, then try to login and try access the files on remove machine.
Unfortunatly I have tried this, I can log into the sql server and the remote machine with the same user name. I have no problem doing this. However I am still unable to get the sql server PC to access the folder on the Application PC.
Terry

So when you are not able to access the files from the SQL server machine even when you are logged on the machine using the SQL service account, then it is a problem of correct rights and or shared folders setting (or other rights configuration). In this case it should not be a problem of SQL server.

Once you can access the files form the SQL Server machine logging into it using the service account, then also SQL server will not have any problem to access the files.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

I think Pavel is right. It has to be security. domain admins don't necessarily have rights to all folders.

What I'd suggest is you make a new share on the other machine, open to everyone, move the file there, try it. If that works, then dig into rights more.

tmacdonald SSC Veteran Points: 215 More actions · Answer 6

Steve Jones - Editor (12/10/2009)
I think Pavel is right. It has to be security. domain admins don't necessarily have rights to all folders.
What I'd suggest is you make a new share on the other machine, open to everyone, move the file there, try it. If that works, then dig into rights more.

Steve,

Actually I already attempted that prior to Pavel posting that option. From what I am reading, I might not have given BulkAdmin rights to the user, so that could be a possible cause. I've seen on the web where this can cause the same error that i'm getting.

I've tried the new share on same machine, different machine-open to everyone and still nothing, so tomorrow morning I am going to try the BulkAdmin right approach and see. I will let you know the results.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 7

I think that you may be confusing SQL Permissions with Windows permissions. You cannot interchange the two. What is the name of the user account that the SQL Service is using? This must be an Active Directory account. If not, then you must use the boxname/username to give that directory permission.

Does that make sense? I have always created a simple AD account(non admin), and that is what I use to run the service for all the sql boxes. That way I can easily give that user permissions to a directory any where on the network. But an account within SQL Server, has nothing to do with the directory permissions. This is also why using a SSIS package would be easy.

Andrew SQLDBA