Last time I wrote a blog about linked server creation issue. As I said, these are one of the most common issues. But as soon as such blogs get released, I get a number of requests around them immediately. Here is the blog post which is discussing about the linked server error.
SQL SERVER – FIX – Linked Server Error 7399 Invalid authorization specification
After reading that one of the readers contacted me and told that he is getting below error.
On first look, I thought this was similar to what was published earlier. But things can surely turn out to be different. Since I had recently blogged about it, I thought to investigate this. To understand more, I looked at the error message in detail. It looks like:
TITLE: Microsoft SQL Server Management Studio
——————————
The linked server has been created but failed a connection test. Do you want to keep the linked server?
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)
——————————
I played around with the linked server settings on the PC just to realize – I was able to reproduce the error by choosing 1st option in security tab. This was simpler than what I thought.
So, I asked him to use either option 2 or option 3. After using right option, the error message disappeared.
Do you remember seeing any such linked server error? Can you share your experience to what happened here this time? Leave a comment as we can learn from each other.
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
HI pinal dave,
Myself vasu, i have to build the azure cluster in cloud but i don’t have experience on azure cluster.I request you to can you suggest any document or link. its really help full to me. [email removed]
I am facing this same issue. but it was succeeded a day before and which was in use as well. now i am getting the error same as the image above. error code 7416
I ran into this error after dropping and recreating the linked server, even though the login mappings were set correctly and “Be made using this security context” was set with correct credentials. Weirdly enough, I fixed it by updating the SQL login referenced in “Be made using this security context” with the same password. So I essentially did not make any changes, but somehow the simple act of updating the login made the connection work again.
Hii pinal,
I didnt find any linked server setup tutotrial ie, How to make our server as a linked server . suppose We dont want to provide full access to our server but only allow to see couple of views for the other party .
I have received this same error when calling a stored procedure from Cognos, but works when running the stored procedure in SSMS.
The stored procedure uses the openrowset command to run another stored procedure that requires extra variables to be provided. Interestingly enough, the error does not occur when I call the main stored procedure with all the variables required by the sub stored procedure, but the sp returns no records.
The error occurs when I run the main stored procedure with only 2 of the required variables, with the other variables declared and set in code within the main stored procedure.
It seems that this is an openrowset issue that I am trying to figure out.
I forgot to mention that the openrowset call of the stored procedure isn’t really calling a linked server, but is using local as the linked server.
Hi Pinal and thank you! I just encountered this issue on SS 2016. The second option resulted in a different error but using the third option (“Login using the current security context”) fixed it.
Previously, the first option (“Not be made”) always worked just fine, also on SS 2016. When it worked, I was an admin on both the origin and target servers, and I was also named as a permitted login (mapped to a SQL login). I’d need to do more testing to figure out the exact pattern. Hope that helps in your info-gathering.
Hello Pinal. I think this resolution here is a bit misleading while it does cause the error do go away it creates a security issue. The purpose of the Not Be Made radio button is to fix the user mapping for the linked server to specific user accounts that have been setup on the destination server. By changing the the options we end up saying use a different account to make the connection. So if you are not concerned about how much access the account has on the destination server that could be, ok but if you are trying to limit the rights of a user launching a query against the destination server then the less secure settings can create security holes. If i have a user that only should be able to update 2 tables on 1 database of the destination server providing that account access as a system administrator on the destination server would be inappropriate.
Currently looking to resolve the issue reported here by keeping the Not Be Made button on.
I have two SQL Servers, both 2016, and am trying to set up a linked server from one to the other.
The linked server is of type “SQL Server” and in the Security section, I’m adding a mapping as Local = DOMAINUSER, Remote = DOMAINUSER and password – this domain user is set up as a login on both servers.
The option “Not be made” is selected from the list below.
However, when trying to create this, I get the error as mentioned.
So I’m a bit puzzled why it doesn’t work…