Named Instance SQL 7 / SQL 2K

We have a server by name TEST and SQL 7 installed and exists a login 'xx'
and password 'xx'
Recently we installed a named instance SQL 2K on TEST by name TEST\TEST2K
and with a login 'xx' and password 'abc'
I created an alias for TEST\TEST2K as TEST2K
From TEST, Executed sp_addlinkedserver TEST2K
When I try executing from TEST , logged in as 'xx' and password 'xx'
SELECT * FROM TEST2K.MYDB.DBO.MYTABLE
error message
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'xx'.
Is this because of the password difference for 'xx' in TEST and TEST2K '
How to handle this '
Thanks
ShamimSee if this helps...
exec sp_addlinkedserver 'TEST2K','','SQLOLEDB','TEST\TEST2K'
exec sp_addlinkedsrvlogin 'TEST2K','false',null,'xx','abc'
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:OV0C$P%23eDHA.1828@.TK2MSFTNGP10.phx.gbl...
> We have a server by name TEST and SQL 7 installed and exists a login 'xx'
> and password 'xx'
> Recently we installed a named instance SQL 2K on TEST by name TEST\TEST2K
> and with a login 'xx' and password 'abc'
> I created an alias for TEST\TEST2K as TEST2K
> From TEST, Executed sp_addlinkedserver TEST2K
> When I try executing from TEST , logged in as 'xx' and password 'xx'
> SELECT * FROM TEST2K.MYDB.DBO.MYTABLE
> error message
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'xx'.
> Is this because of the password difference for 'xx' in TEST and TEST2K '
> How to handle this '
> Thanks
> Shamim
>|||Thanks oj..It worked !!!! much appreciated.
Shamim
"oj" <nospam_ojngo@.home.com> wrote in message
news:e$v$do$eDHA.2748@.TK2MSFTNGP11.phx.gbl...
> See if this helps...
> exec sp_addlinkedserver 'TEST2K','','SQLOLEDB','TEST\TEST2K'
> exec sp_addlinkedsrvlogin 'TEST2K','false',null,'xx','abc'
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> news:OV0C$P%23eDHA.1828@.TK2MSFTNGP10.phx.gbl...
> > We have a server by name TEST and SQL 7 installed and exists a login
'xx'
> > and password 'xx'
> > Recently we installed a named instance SQL 2K on TEST by name
TEST\TEST2K
> > and with a login 'xx' and password 'abc'
> >
> > I created an alias for TEST\TEST2K as TEST2K
> > From TEST, Executed sp_addlinkedserver TEST2K
> >
> > When I try executing from TEST , logged in as 'xx' and password 'xx'
> >
> > SELECT * FROM TEST2K.MYDB.DBO.MYTABLE
> >
> > error message
> > Server: Msg 18456, Level 14, State 1, Line 1
> > Login failed for user 'xx'.
> >
> > Is this because of the password difference for 'xx' in TEST and TEST2K
'
> >
> > How to handle this '
> >
> > Thanks
> > Shamim
> >
> >
>

Name: dbo Login name: none ??

Sometimes I find that when I restore a database the user
(sa is the only user in the database in this case) comes
up with the "Name" of dbo but there is nothing displayed
in the "Login Name" column in the "users" object. How do I
get the "Login Name" of sa to appear there?
Thanks
JThe login mapping for the 'dbo' user is determined by database ownership.
This can get out-of-sync following a restore or attach. You can correct
this with sp_changedbowner:
USE MyDatabase
EXEC sp_changedbowner 'sa'
GO
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:04e701c3b9e8$ced2ab50$a101280a@.phx.gbl...
> Sometimes I find that when I restore a database the user
> (sa is the only user in the database in this case) comes
> up with the "Name" of dbo but there is nothing displayed
> in the "Login Name" column in the "users" object. How do I
> get the "Login Name" of sa to appear there?
> Thanks
> J

Mysterious login failure error message in log-shipping

Hi I posted this a few days ago in SQL Server Security forum and at Raul Garcia's suggestion (thanks Raul for your help), I post my question here again to see whether I can be lucky enough to find a super guru to help me out.

I get the following error messages in the sql server error log

Source Logon

Message
Error: 18456, Severity: 14, State: 11.

and

Source Logon

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]
(note: 185.23.11.33 is the ip addr of the source server, i.e. node 1 in the two node clustered environment)

The scenario is: We set up log-shipping (LS) between a clustered sql server system (source server) and a stand-alone sql server box (target server). (SQL Server 2K5 EE + SP1 and OS is Win2K3 + SP1), and LS goes very well, but on the target server, we found the above-mentioned error messages. The messages seem in synchronization with Log shipping because we setup log shipping cycle at 10 minutes (from 12:00:00 am to 11:59:00pm), and I can see every 10 minutes, for example at 12:10:00am, the login failure errors occur in the target server's sql server error log, and then not appear until 12:20:00am.

BTW: the two servers are in the same domain.

Thanks in advance for your help ...

Jeff

Very much a case of delegation problem in linked server. Please take a look at Nan Tu's blog for an excellent info.
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx|||

Thanks oj, you are my hero...

I believe it is true as I can find there is a linked server on my source server that points to the target server, where the error message is logged. But just curious why SQL Server automatically set up a linked server for log shipping.

|||Logshipping is functional through linked server. It's by design.

If you use built-in logshipping, the wizard will create the linked server for you. If you set up log shipping by hand (when you run on Standard edition), you will have to setup the linked server yourself.

Mysterious login failure error message in log-shipping

Hi I posted this a few days ago in SQL Server Security forum and at Raul Garcia's suggestion (thanks Raul for your help), I post my question here again to see whether I can be lucky enough to find a super guru to help me out.

I get the following error messages in the sql server error log

Source Logon

Message
Error: 18456, Severity: 14, State: 11.

and

Source Logon

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]
(note: 185.23.11.33 is the ip addr of the source server, i.e. node 1 in the two node clustered environment)

The scenario is: We set up log-shipping (LS) between a clustered sql server system (source server) and a stand-alone sql server box (target server). (SQL Server 2K5 EE + SP1 and OS is Win2K3 + SP1), and LS goes very well, but on the target server, we found the above-mentioned error messages. The messages seem in synchronization with Log shipping because we setup log shipping cycle at 10 minutes (from 12:00:00 am to 11:59:00pm), and I can see every 10 minutes, for example at 12:10:00am, the login failure errors occur in the target server's sql server error log, and then not appear until 12:20:00am.

BTW: the two servers are in the same domain.

Thanks in advance for your help ...

Jeff

Very much a case of delegation problem in linked server. Please take a look at Nan Tu's blog for an excellent info.
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx|||

Thanks oj, you are my hero...

I believe it is true as I can find there is a linked server on my source server that points to the target server, where the error message is logged. But just curious why SQL Server automatically set up a linked server for log shipping.

|||Logshipping is functional through linked server. It's by design.

If you use built-in logshipping, the wizard will create the linked server for you. If you set up log shipping by hand (when you run on Standard edition), you will have to setup the linked server yourself.

Mysterious login error message

I get the following error messages in the sql server error log

Source Logon

Message
Error: 18456, Severity: 14, State: 11.

and

Source Logon

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]

The scenario is: We set up log-shipping (LS) between a clustered sql server system (source server) and a stand-alone sql server box (target server). (SQL Server 2K5 EE + SP1), and LS goes very well, but on the target server, we found the above-mentioned error messages.
BTW: the two servers are in the same domain.

Did I miss something in configuration?

Thanks in advance for your help..

Jeff

It looks like a delegation problem. Most likely the credentials from a 3rd machine (most likely the client) are used to connect to the source SQL Server are being used to connect to the target SQL Server. If this is the case, the following articles will hopefully help you to fix it:

Security Account Delegation http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp

Specifying Credential and Connection Information http://msdn2.microsoft.com/en-us/library/ms160330.aspx

Let us know if this information helped.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

I do not think we have a 3rd machine/application to connect to the source server / or the target server. Actually, we setup both source server and target server (in our log-shipping scenario) to use the same domain account as SQL Server service and sql server agent service startup account.

For the 1st reference link you mentioned, i.e. Security Account Delegation http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp our environment is Win 2K3 EE + SP1 (64 bit) and SQL Server 2K5 EE + SP1 (64bit), so it seems unapplicable here.

Personally I believe it is has something to do with the log-shipping, because we setup log shipping cycle at 10 minutes (from 12:00:00 am to 11:59:00pm), and I can see every 10 minutes, for example at 12:10:00am, the login failure errors occur in the target server's sql server error log, and then not appear until 12:20:00am.

Do you have some other thoughts regarding this?

Thanks a lot for your help, which is greatly appreciated...

BTW, in the error message

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]
Here [CLIENT: 185.23.11.33] is the ip address of our source server (node 1 in two clustered server environment)

|||

Unfortunately I cannot think of any other reason why you would get this particular error message; hopefully somebody else may have a better answer. Another option could be to post your question on the SQL Server Database Engine forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1), hopefully there we may find somebody with more knowledge on log-shipping who may be able to answer your question.

-Raul Garcia

SDE/T

SQL Server Engine

|||With the help from MVP oj, I finally figured it out. The error appeared because a linked server (on the source) was created by the Log-shipping wizard ( but I still do not understand why Log-shipping needs a linked server), and after configured the linked server by giving a remote user with password, the error disappears.

my SS name changed withou re-install...strange!!

i think a lot of people using SQL Server 2005 Express has already used to use 'sqlcmd -S .\SQLEXPRESS' to login to the SS or using '.\SQLEXPRESS' as the Server Name to login in to the Management. but today i found that i cant login to my SS using '.\SQLEXPRESS', instead i must use the full name of my computer...that quite a long name and it's really inconvenient, could anyone help me out?

i think that the '.' in '.\SQLEXPRESS' is a short-cut for my computer name, but why i cant us it now? i was using it last week!

PS: what is the value of 'log on as' in ther property page of SQL Server 2005? i remember it is 'local service', but my current value is 'network service'

hi,

DavidShen wrote:

i think a lot of people using SQL Server 2005 Express has already used to use 'sqlcmd -S .\SQLEXPRESS' to login to the SS or using '.\SQLEXPRESS' as the Server Name to login in to the Management. but today i found that i cant login to my SS using '.\SQLEXPRESS', instead i must use the full name of my computer...that quite a long name and it's really inconvenient, could anyone help me out?

i think that the '.' in '.\SQLEXPRESS' is a short-cut for my computer name, but why i cant us it now? i was using it last week!

yes... "." or "(Local)" are shorkeys for the actual local computer, and they both should run...

PS: what is the value of 'log on as' in ther property page of SQL Server 2005? i remember it is 'local service', but my current value is 'network service'

these are accounts under which the actual service is run... "network service" is more limited than "local service", but they both are way to powerfull than required... you can have a look and perhaps modify the permissions "required"..

regards

|||

hi,

i can log in with 'localhost\SQLEXPRESS' or '(local)\SQLEXPRESS', but not '.\SQLEXPRESS'...really strange!! maybe there're some reg-key error, but i cant find it!!!

even though, 'localhost' and '(local)' is much shorter and easier to remember than my full computer name, i still wish to use the old way...

pllllllllllzzzzzzzzz help!

my SS name changed withou re-install...strange!!

i think a lot of people using SQL Server 2005 Express has already used to use 'sqlcmd -S .\SQLEXPRESS' to login to the SS or using '.\SQLEXPRESS' as the Server Name to login in to the Management. but today i found that i cant login to my SS using '.\SQLEXPRESS', instead i must use the full name of my computer...that quite a long name and it's really inconvenient, could anyone help me out?

i think that the '.' in '.\SQLEXPRESS' is a short-cut for my computer name, but why i cant us it now? i was using it last week!

PS: what is the value of 'log on as' in ther property page of SQL Server 2005? i remember it is 'local service', but my current value is 'network service'

hi,

DavidShen wrote:

i think a lot of people using SQL Server 2005 Express has already used to use 'sqlcmd -S .\SQLEXPRESS' to login to the SS or using '.\SQLEXPRESS' as the Server Name to login in to the Management. but today i found that i cant login to my SS using '.\SQLEXPRESS', instead i must use the full name of my computer...that quite a long name and it's really inconvenient, could anyone help me out?

i think that the '.' in '.\SQLEXPRESS' is a short-cut for my computer name, but why i cant us it now? i was using it last week!

yes... "." or "(Local)" are shorkeys for the actual local computer, and they both should run...

PS: what is the value of 'log on as' in ther property page of SQL Server 2005? i remember it is 'local service', but my current value is 'network service'

these are accounts under which the actual service is run... "network service" is more limited than "local service", but they both are way to powerfull than required... you can have a look and perhaps modify the permissions "required"..

regards

|||

hi,

i can log in with 'localhost\SQLEXPRESS' or '(local)\SQLEXPRESS', but not '.\SQLEXPRESS'...really strange!! maybe there're some reg-key error, but i cant find it!!!

even though, 'localhost' and '(local)' is much shorter and easier to remember than my full computer name, i still wish to use the old way...

pllllllllllzzzzzzzzz help!