January 16, 2006 at 4:13 am
Hi,
Firstly apologies but I am no expert in SQL!
We have two SQL 2000 servers. (Server1 & Server2 for sake of argument.)
Server2 was rebuilt from scratch and renamed recently.
Server1 has a large number of VB scripts to pull data from Server2.
These scripts failed and on investigation I noticed that they connected to Server2 using a SQL account - not a domain account:
ConnectStr = "Driver=SQL Server;SERVER=Server2;uid=expressuser;pwd=password;DATABASE=express"
I created this user within SQL on Server2 and set the server authentication mode to mixed mode and rebooted the server but the scripts still failed.
I also amended the existing ODBC connector on Server1 to point to the new Server2 name - this tests OK.
I tried connecting to Server2 from the Query Analyser on Server1 - this works fine using Windows authentication but fails when using SQL authentication and the user & password created within SQL on Server2.
The error message is:
Unable to connect to server Server2:
Server: Msg 18452, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'expressuser'. Reason: Not associated with a trusted SQL Server connection.
I did a bit of digging on this and tried adding a Linked Server in SQL from Server2 to Server1:
Server Type = SQL server
Connection made: Using the login's current security context.
I can see tables and views in the Linked Server view but still cannot connect to Server2 using SQL authentication from Query Analyzer on Server1.
TCP/IP and DNS etc are all tested and working fine.
Both servers are Windows 2003 with the same hot fixes (not SP1).
Server 1 = SQL 2000: 8.00.760 = SP3
Server 2 = SQL 2000: 8.00.2039 = SP4
I can successfully connect from Query Analyzer on Server2 to Server1 using SQL authentication!
But cannot connect from Query Analyzer on Server1 to Server2 using SQL authentication!
Connecting via Query Analyser using Windows authentication works fine in both directions.
I've created additional SQL test users on Server2 and tested with them but still get the same results.
I can't see any differences between the set up of the two servers (other than SP level) unless there is something burried deep within SQL (permissions / security?).
What do I need to do to be able to connect from Server1 to Server2 using SQL authentication????
Many Thanks!
Anthony.
January 16, 2006 at 9:04 am
Anthony;
It certainly sounds like your Server2 mixed mode authentication is not set. All cases that you specified indicate that problem on Server2. I suggest double-checking Authentication mode on Server2.
You mention creating link server on Server2 to Server1. Have you tried the reverse, using the Server2 SQL login?
Hope this helps
Mark
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply