February 20, 2009 at 3:50 pm
So here is the situation I am running into. Currently I am working with 05 and 08 installs on SQL. I have created a program that manages the install of a DB on a local machine. When you install it first creates the DB, then tables (no Keys), then it uses Bulk insert to import the data into those tables. When you are on that machine it works fine. If I copy the install to the network and run the install from my machine trying to install on a remote machine it fails on the bulk insert. It gives the error "Access Denied" I have read tons of posts about this and I am not the only one to see this. We worked with the permissions and it still does not work. I have been told that we need to support this case where a user can run it locally to install it remotely. Is there any work around. BCP seems to do the same thing.
February 24, 2009 at 7:31 am
Check if you can access the remotely created DB with the credentials used by the connection using SSMS.
I guess you must create a user on the new database (and/or reconnect to the db) before inserting the data.
February 24, 2009 at 8:54 am
Also check under what user the SQL Service is running. Is it running under Local User or Network user.
-Roy
February 24, 2009 at 8:55 am
I suppose what Hanshi is saying, trying to connect with the credentials that are used to run the application would be one of my first steps for troubleshooting
February 24, 2009 at 9:01 am
duda (2/24/2009)
I suppose what Hanshi is saying, trying to connect with the credentials that are used to run the application would be one of my first steps for troubleshooting
To install something over the network, the user has to be an Admin and since the SQL server is being installed and create DB is also working, I dont think it is the user that is running the app that is the problem.
Also when the SQL server is installed, the BUILDIN/Administrator group is SysAdmin. So I do not see any need for a DB user to be created. (Unless of course the user it uses is a DB User.
-Roy
February 24, 2009 at 9:18 am
Roy is correct. It has something to do beyond the user executing the application. I have seen a few posts and I do not think this will work. It seems to be a glitch in Bulk insert and BCP. We have permissions all over. We have even mapped the drive going back to my machine for testing purposes.
February 24, 2009 at 9:27 am
This is something that I read on BOL. I dont know if it is applicable in your case.
"When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.
To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help."
For BCP BOL says "Bulk importing data into a remote table is not supported."
-Roy
February 24, 2009 at 11:30 am
Roy thanks for the post. That is what I read yesterday. Here is another post that discusses this
http://www.sqlservercentral.com/Forums/Topic325966-5-2.aspx
I can see a way to get around this but it is not what management wanted. Personally I do not see the problem with remoting in.
February 25, 2009 at 12:50 am
Tx Roy, you are correct. JKSQL, was your work around based around this or did you try something else?
February 25, 2009 at 12:15 pm
Yes what I have documented is if the user wants to install this way they will have to create a standard account to have the permissions work on the network directory. basically our software that will install the DB and data scripts will have to be local or remoted into the machine.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply