Skip to main content

Remote Login Failure on Server

I ran into an interesting situation when executing a Remote Stored Procedure on Linked Server using four part query.  Vista-3139 which is a SQL Server 2005 instance is linked to the SERV2003\SQLDUMMY which is a SQL Server 2000 named instance.
clip_image002
When I tried to execute a stored procedure:
EXEC [SERV2003\SQLDUMMY].SQLAdmin.dbo.sp_ddlevents Got the following error message.
Could not connect to server SERV2003\SQLDUMMY ' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
I checked the Security settings of the linked server and it was set to “Be made using the login's current security context", which is valid considering that the user has all the permissions to login to the remote SQL server.
clip_image003
Then I changed the security context to use a SQL authentication, but still it failed with the similar error message as described above. To troubleshoot this further, I ran the query select * from master..sysservers and here's how the output looked like.
clip_image005
Now this field isremote caught my attention. So what does this value isremote mean ?
Well, According to Books Online the column isremote is 1 if server is a remote server, else 0 if server is a linked server.  Linked servers are a superset of "remote servers." Remote servers allowed the running of stored procedures on distributed SQL Server machines. SQL 2000 BOL states, "Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead."
What does this mean in terms of a valid setting to execute a stored procedure over a linked server?
isremote = 1 indicates that Execute Query Fails
isremote = 0 indicates that Execute Query Works
Since isremote value is a part of the system tables of the Master Database, we cannot directly modify it to change its value. Dropping the linked server (SERV2003\SQLDUMMY) and re-creating the linked server resolved the issue.  The isremote value for the linked server which had been set to 1, after dropping and recreating the linked server the isremote value now was set to Zero.
clip_image007
This is applicable in both SQL Server 2005 and SQL Server 2008 environments. SQL Server 2005, 2008 equivalent of sysservers is sys.servers which has a column is_linked.  You can check this by running this
       Query select * from sys.servers
Another interesting that I stumbled across while working on this issue is that in a SQL Server 2000 scenario, the isremote column values are inverted.
clip_image009
Once again running the query, select * from master..sysservers shows the results as below :
clip_image011
Thus as seen above the value of isremote value of the linked server (VISTA-3139) = 1 is valid in a SQL Server 2000 linked server.

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

SQL Server

                                                                     SQL Server  Its been a while that I have updated my blog. Though Databases and SQL was something that I used to stay away as much as possible because for some reasons, I got to work on these all the day for 8-9 hours and sometimes haunting in the nights and weekends as well. However, it has been a good journey so far and I found some interesting stuff in SQL Server. So, this triggered an idea a couple of weeks back to share some cool stuff that I am learning in SQL Server over numerous sources. Hope I can make this a good series. As there are not much visitors to my blog, le...
4 comments
Read more

An TLS 1.2 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The TLS connection request has failed.

If the certificate being used on the server was generated using the Legacy Key option in the certificate request form, the private key for that certificate will be stored in Microsoft's legacy Cryptographic API framework. When the web server tries to process requests using its new, Cryptographic Next Generation (CNG) framework, it appears that something related to the RSA private key stored in the legacy framework is unavailable to the new framework. As a result, the use of the RSA cipher suites is severely limited. To avoid the issue, you can try to generate the certificate request using the CNG Key template in the custom certificate request wizard.
Post a Comment
Read more

One-third of people 'would upgrade' to a Facebook phone - is Zuckerberg's latest project a serious rival to iPhone?

One-third of phone users would definitely upgrade to a Facebook phone  - and 73% think the phone is a ‘good idea’. News of the phone emerged, with sources claiming that Facebook had hired ex-Apple engineers to work on an ‘official’ Facebook phone.     It’s not the first Facebook phone to hit the market – the social network giant has previously partnered with INQ and HTC to produce Facebook-oriented handsets, including phones with a  built-in ‘Like’ button. It could be a major hit – a flash poll of 968 people conducted by MyVoucherCodes found that 32% of phone users would upgrade as soon as it became available. The key to its success could be porting apps to mobile – something Facebook is already doing. Separate camera and chat apps already separate off some site functions, and third-party apps will shortly be available via  a Facebook app store. Of those polled, 57% hoped that it would be cheaper than an iPhone – presumably support...
Post a Comment
Read more