Score:0

Can I use an UNC path on remote MSSQL server in a store procedure to access remote file?

br flag

I have a store procedure on a remote MSSQL server. It has bulk insert from a file.That file is on another server. The file is shared on Ubuntu via Samba.

Can that store procedure access my file? What user SQL server will use, SQL instance running user or my db user so I can add him to the Ubuntu server share?

Score:1
cn flag

According to the docs, you can use a UNC path in the BULK INSERT statement.

As to what user will be used, I'd expect it to use the account that's running your SQL service. Think of it this way - it's the OS accessing the file. The OS has no notion of your internal database user.

Hrvoje T avatar
br flag
It's a shared hosting, not of my own server. Is there a way I can find out what is that user by just looking pproperties of my database in mssms? I don't think admins would share that info with me. Can I share with 'everyone' via samba so that user is not important?
Ben Thul avatar
cn flag
Take a look at `select * from sys.dm_server_services;`. If you can run it, you should find what you're looking for in there. If not (which I could see if your hosting company hasn't granted you sysadmin or equivalent in order to be able to view this data), it's either ask your hosting company or find out via other means. E.g. does the samba server log failed attempts? If so, can that info be used to back into who's making the request? I can't in good conscience *recommend* granting access to Everyone but that would certainly work.
Hrvoje T avatar
br flag
`Msg 300, Level 14, State 1, Line 1 VIEW SERVER STATE permission was denied on object 'server', database 'master'. Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.` ... so no rights for me. However, I managed to make bulk insert with UNC path and Samba public share.
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.