1

I am moving a SQL database from my C:\ to a Server and I'm a little new at this. I use Openrowset and Opendatasource to get data from Excel, Access, and CSV files but now it only works with a fixed drive (C:\ or E:). I am trying to use xp_CmdShell to map the drive and here is what I get:

First, I ran this:

exec sp_configure 'xp_cmdshell', 1; 
reconfigure

Then this:

exec xp_cmdshell 'net use G: \\172.16.0.32\Finance'

and got

The command completed successfully. NULL NULL

Then I run:

exec xp_cmdshell 'net use'

and get:

New connections will be remembered. NULL NULL Status Local
Remote Network NULL ------------------------------------------------------------------------------- OK G: \172.16.0.32\Finance Microsoft Windows Network The command completed successfully. NULL NULL

But I run this and get an error:

exec xp_cmdshell 'Dir G:'

An unexpected network error occurred. NULL

If anyone can help it would be huge!

Update: I deleted the drive and remapped it, rebooted the server and restarted the service

exec xp_cmdshell 'net use G: /delete' exec xp_cmdshell 'net use G: \172.16.0.32\Finance'

Then I got this:

exec xp_cmdshell 'Dir G:'

Volume in drive G is FileSharing1 Volume Serial Number is 20E1-DEF3 NULL Directory of G:\ NULL 06/22/2017 08:56 AM . 06/22/2017 08:56 AM .. 09/20/2017 09:37 AM
IMA Utilization Reports 03/09/2017 06:36 PM
Microsotf SQL Server 2014 02/15/2017 06:22 PM
Wellcare-Apixio Project 0 File(s) 0 bytes 5 Dir(s) 722,791,567,360 bytes free NULL

After all that my OPENROWSET Query still does not work

anonymous
  • 113
  • 13

1 Answers1

0

I believe you have a permission issue in the following way:

When you install SQL Server it will ask you to configure the OS account under which the SQL Server service will run. If that option is not changed you are running with a default account, and the mean of a default account in this context has changed a bit in recent versions.

In the last versions (2016/2017) it means your service will run with a special service account which is named after the service name. In my dev machine the service is named MSSQL$DEV so my service runs under the "NT Service\MSSQL$DEV" account. If your service is 2008 or newer you can query the sys.dm_server_services view to find out the account in question. There's a column called service_account.

The case with those service accounts is that they usually don't have access to the network. So in your case SQL Server might not be able to access the \\172.16.0.32\Finance share.

Now you mention xp_cmdshell. You might be able to make xp_cmdshell work by configuring a proxy account, but I believe your ultimate goal is to be able to access remote data with OPENROWSET and the likes. And the proxy account for xp_cmdshell won't be any good for OPENROWSET.

So what can you do? When you connect to SQL Server using Windows authentication, SQL Server will usually try to impersonate you when accessing external resources, such as files and network shares. You might need to configure Kerberos delegation and such but it certainly works.

If you connect to SQL Server using a SQL account (login/password) or can't get the delegation thing to work, another option is changing the account under which SQL Server runs to a domain account that has permissions to access the shares and files you need. Do not use a domain admin, just a regular domain account. The best way to change the service account is by using SQL Server Configuration Manager.

Hope it helps.

kirchner
  • 974
  • 6
  • 12
  • Thanks for spending the time putting that together. Ill give it a try. – anonymous Oct 31 '17 at 21:29
  • Which account is the domain account? – anonymous Oct 31 '17 at 21:45
  • @anonymous Typically it is a domain account created to run the SQL server service. You can check your services to see what it currently runs under. – SQLChao Nov 01 '17 at 03:56
  • I configured Kerberos delegation and it works, but only under the sa account using SQL server authentication. Do you know how I can get it to work using Windows Authentication? – anonymous Nov 09 '17 at 20:16
  • I did that before but it requires many steps which I always need to go back to the docs and check. These two articles are not exactly your problem but they do talk about delegation and many hops: https://blogs.msdn.microsoft.com/sqlupdates/2014/12/05/sql-server-kerberos-and-spn-quick-reference/, http://www.itprotoday.com/microsoft-sql-server/implement-kerberos-delegation-ssrs-0. Also a web search for "troubleshooting sql server kerberos delegation" brings up lots of useful blogs/docs/articles. Hope it helps. – kirchner Nov 09 '17 at 20:22
  • When I add a userid and pwd to the opendatasource query it gives me the could not find an installable isam message. Any ideas? – anonymous Nov 09 '17 at 20:51