August 4, 2010 at 10:46 am
I am very confused by my situation. It is as if the SQL Server agent of an older unnamed instance is somehow connected to a new named instance on the same server.
What I have: a Windows 2003 R2 64-bit SP2 sever with SQL 2005 SP3 (9.0.4053). I ran with a single default instance for a year, then recently installed a second named instance, exactly same build. At the time of installing the second instance I detached about 20 databases from the unnamed instance and attached to the new instance. All SQL applications are running fine, accessing their database on the respective instance. I am having no problems with the named instance. All SQL service accounts run under a domain service account. A different service account is used for each instance.
My problem: 1) SQL Server Agent Jobs on the old (unnamed) instance do not run, and log the errors
[298] SQLServer Error: 18456, Login failed for user 'DOMAIN\SVCSQL1' [sqlstate 28000] and
[382] Logon to server 'SERVER' failed (ConnAttemptCachableOp)
Well, this seems like just an authentication problem. But:
2) all of the SQL services for the unnamed instance run under the same service account, and I can stop / start the services at will, so password is OK.
3) In the Application event log I get a Failure Audit at exactly the time that I run a SQL job on the unnamed server, but the source is the Named SQL server instance, not the expected unnamed instance.
Login failed for user 'DOMAIN\NAMEDSVC'. [CLIENT: <serverIPaddress>
4) Backup Exec SQL agent lists, for the unnamed SQL instance, only the databases mounted on the named SQL instance, and for the named instance it also lists the databases mounted on the named SQL instance. So the databases of the unnamed instance are not listed.
5) I have no problem backing SQL databases from studio manager, right clicking on the database and selecting database.
So it seems to me that somehow there is an incorrect connection pointing the old SQL Server Agent to the new named instance.
Properties of the SQL Server Agent does not list much - at least the log files are correctly listed in separate directories for the two instances.
Thank you for any insight / ideas! Cam
August 4, 2010 at 1:39 pm
Even though this post is for SQL 2000, check if it helps.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 6, 2010 at 2:16 pm
Good work Pradeep!! The answer was a SQL alias entry. I had opened a Microsoft support case after posting here, and could not find where a SQL alias might be set, anyhow. Next time I will know. So here is the answer:
Start; All Programs; Microsoft SQL Server 2005; Configuration Tools; SQL Server Configuration manager: Navigate through SQL Server Configuration Manager; SQL Native Client Configuration; Aliases
In my case two aliases had been defined, one with the name of the default SQL instance, the other with the named SQL instance, and both referenced the same port number (that of the named instance). And that is why my Backup software was getting the database list for the named SQL instance, regardless of which instance it connected to, and why the SQL maintenance jobs for the default instance failed with errors logging into the named instance.
And why the alias entries? that would be for the development team!
Thanks for your help.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply