CODEWITHSUNDEEP
sqlexcelssmslinked-server
Karl
Karl

Reputation: 189

Unable to connect to Linked Server (Excel file) when using SQL account

The Plan

Have HR personnel dump an Excel file of payroll Job Titles to a network share, add that file as a Linked Server in SSMS, use those titles in my ASP.Net web forms. Using a Linked Server instead of importing data to allow HR to update the Job Titles file at their discretion.

The Problem

I can create the Linked Server and query it without issue, so long as I'm in SSMS with Windows Authentication. But, if I try to access the linked server using a SQL account, I first get the error:

Access to the remote server is denied because no login-mapping exists (Error 7416)

The SQL account being used is the same account my web forms use for everything else. The fact that this SQL account doesn't have file permissions isn't surprising though, so an adjustment to the Linked Server's security should do the trick.

The Problem (part 2)

To reduce the number of variables, I moved the Excel file so it's on the same machine as my SQL Server. I'm logged into the machine and logged into SSMS with my domain admin account.

I access the Linked Server's security tab and, to try and cast the widest net possible, I leave the local login (top part) blank and head straight to "Be made with this security context:" and proceed to provide my domain admin credentials. I hit OK, and I get the following:

Not a valid account name or password (Error 7399)

I know the account name and password are good, so what gives?

Other Things

Folks who've ran into similar things have been instructed to change the logon account being used for the SQL Server service, which I've done using my domain admin account (for troubleshooting, atm). This did not fix the problem.

This is what the Linked Server code looks like, although I created it via the GUI (included for sake of completeness):

EXEC master.dbo.sp_addlinkedserver @server = N'ADP_TITLES', @srvproduct=N'', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\JOB_TITLE_EXPORT.xlsx', @provstr=N'Excel 12.0 Xml'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADP_TITLES',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN\username',@rmtpassword='########'

Upvotes: 0

Views: 1191

Answers (1)

Karl
Karl

Reputation: 189

The solution appears to be entering "admin" for the remote login, and leave the password blank.

This only works, however, if the file is stored locally. If it's on the network, I still get a 7399 error, but instead of saying the issue is the account name/password, it now says the issue is unspecified.

I can make my project work with a local file though. Since I'll be moving forward with that, I have no need to solicit additional answers.

Upvotes: 2

Related Questions