February 21, 2006 at 7:25 am
Hi all,
I have created a linked server between two SQL2000 servers. One of then has W2K (server1) and the another has W2K3 (server2). Both of them are linked servers between themselves.
The linked servers properties are the same in both servers:
The query from server1(W2K) to server2(W2K3) always is success but in the opposite direction doesn't work properly.
The query is very simple: select * from server1.db1.owner.table
I connect by QA to the server2 with Windows authentication and I launch the query against server1 I haven't results and I receive the next error message 'SQL Server does not exist or access denied.'
BUT, when I open QA and firstly I connect to the server2 with SQL Server Authentication there isn't problem, I launch the query and I have results, and later I connect to the server2 with Windows authentication and I launch the same query and I have results too. I don't understand anything, because the query is the same.
I don't know if when I connect firstly with SQL Authentication the server does something, because if I connect firstly with Windows authentication doesn't work.
I thought that the server2 with W2K3 could be the problem and I see the next policies and his properties:
Network access: Do not allow anonymous enumeration of SAM accounts: Disabled
Network access: Do not allow anonymous enumeration of SAM accounts and shares: Disabled
Network access: Let Everyone permissions apply to anonymous users: Enabled
Network access: Restrict anonymous access to Named Pipes and Shares: Disabled
I tested from EM both servers too and I think the linked server configuration is well because I see tables and views of master database of the other server (with sa user registration).
Summarizing, I must connect always firstly with SQL authentication and to launch the query and after to launch the query with Windows authentication.
I thought to run the SQL Profiler but I haven't any experience with this tool.
Any suggestions are welcome.
Thanks
February 21, 2006 at 7:47 am
It seems the SQL login has the proper permission to server1.db1.owner.table. Make sure the Windows login has the same permission as SQL login.
February 21, 2006 at 8:31 am
Yes, the windows login has the same permissions as SQL login.
In fact, when you launch the query after the connection with SQL login then the query work perfectly.
I think that SQL authentication opens something that windows authentication needs.
If I restart my computer and then I opens again QA with windows authentication the query doesn't work.
Thanks
February 22, 2006 at 8:00 am
Make sure you have MS-DTC enabled on your Windows 2003 server. It is not installed by default.
February 22, 2006 at 10:34 am
Erich is correct. Here is a article for the settings that he is referring to. http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
You will or should see error 7391 in the log files.
BK
February 23, 2006 at 3:55 am
I hadn't seen error 7391 in the log files (SQL log or Event Viewer). However I saw wether I had MS-DTC enabled and it was enabled but in Security Configuration I hadn't activated Network DTC Access for working with Network Transactions.
I put all options like says Microsoft issue and then I re-started the server.
The problem was the same.
I must connect always firstly with SQL authentication and to launch one simple query and after to launch all my queries with Windows authentication.
One question, if MS-DTC wouldn't be configured with Network Transactions enabled ... I don't know if the first query that I launch with SQL login shouldn't work. I don't understand.
Thanks
UPDATE: I've just installed another server with W2K3 and SQL Server 2000 and I have enabled MS-DTC and I have the same problem.
I'm sure that is something about W2K3 because whether I launch queries from W2K server to W2K3 server through Linked Server I have not the problem. I can launch distributed queries connecting firstly with Windows Authentication.
February 23, 2006 at 9:18 am
What are you settings in MSDTC? Have you tried allowing anonymous connections within your MSDTC properties, inbound and outbound. I know it will not be the most secure but it may head you in the right direction as to where you issue lies. The user that you are longing in with, does it exist on both servers involved?
BK
February 23, 2006 at 12:38 pm
On server 1, make sure the SQL login exist and that the access right is given to the desired database.
February 24, 2006 at 2:10 am
My MSDTC settings are:
I don't find where I can allow anonymous connections within MSDTC properties, inbound and outbound.
However, if all this options (about MSDTC and so on) are necessary for Linked Servers then I don't understand why I can launch queries with Windows Authentication after launching queries with SQL Authentication.
I think that never I could launch queries through Windows login. I am thinking that when I launch queries with SQL login at the beginning, this connections does something that later I can launch the other queries.
And of course, the SQL login exist and that the access right is given to the desired database. I think that if this login didn't exist I never can connect neither SQL login nor Windows login.
I don't know how I will be able to resolve it.
Thanks.
UPDATE: As far as I know I allow anonymous acces in W2K3 Policies. You can see this options in a previous post.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply