Login failed for user 'x', Error: 18456
in [Programming SQL]
|
From: Tibor Karaszi on 2 Mar 2010 10:30 I would still check if the database owner is orphaned. Check sys.databases and see whether the sid for the owner is who it should be. Then do a SELECT from sys.server_principals and inside your "strange" database sys.database_principals. Check the dbo user, that this has the expected SID number to match in sys.server_principals. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jeff" <it_consultant1(a)hotmail.com.NOSPAM> wrote in message news:OWADMbhuKHA.732(a)TK2MSFTNGP06.phx.gbl... > Yes, I've checked it, except the SID thing wasn't sure about how to do it. > > But regarding orphaned users: I've created a dozens users (new database > login and create user which I assign to the new login)... All gives the > same error : Login failed for user... I've checked many times that > database, username etc is spelled correct > > I found this in the errorlog, maybe it can help find the error: > The SQL Network Interface library could not register the Service Principal > Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to > register an SPN may cause integrated authentication to fall back to NTLM > instead of Kerberos. This is an informational message. Further action is > only required if Kerberos authentication is required by authentication > policies. > > And this is from errorlog aswell: > Server is listening on [ 'any' <ipv4> 4344] > > any suggestions? > > > "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote > in message news:udX%23ZQKuKHA.5940(a)TK2MSFTNGP02.phx.gbl... >> Did you check the things I asked/suggested in my last reply? >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "Jeff" <it_consultant1(a)hotmail.com.NOSPAM> wrote in message >> news:#G4dn4GuKHA.1796(a)TK2MSFTNGP02.phx.gbl... >>> That method gives my name, not the database user I try to connect to db >>> with. >>> >>> So the string is "Jeff on PC3" (I called my computer PC3, not a very >>> original name) >>> >>> >>> > >
From: Jeff on 2 Mar 2010 11:19 I made a search from sys.databases, there I found owner_sid of the database. Then I checked in server.princials and I didn't find the sid in the sid column. "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:uSXvR0huKHA.3428(a)TK2MSFTNGP06.phx.gbl... >I would still check if the database owner is orphaned. Check sys.databases >and see whether the sid for the owner is who it should be. Then do a SELECT >from sys.server_principals and inside your "strange" database >sys.database_principals. Check the dbo user, that this has the expected SID >number to match in sys.server_principals. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Jeff" <it_consultant1(a)hotmail.com.NOSPAM> wrote in message > news:OWADMbhuKHA.732(a)TK2MSFTNGP06.phx.gbl... >> Yes, I've checked it, except the SID thing wasn't sure about how to do >> it. >> >> But regarding orphaned users: I've created a dozens users (new database >> login and create user which I assign to the new login)... All gives the >> same error : Login failed for user... I've checked many times that >> database, username etc is spelled correct >> >> I found this in the errorlog, maybe it can help find the error: >> The SQL Network Interface library could not register the Service >> Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. >> Failure to register an SPN may cause integrated authentication to fall >> back to NTLM instead of Kerberos. This is an informational message. >> Further action is only required if Kerberos authentication is required by >> authentication policies. >> >> And this is from errorlog aswell: >> Server is listening on [ 'any' <ipv4> 4344] >> >> any suggestions? >> >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote >> in message news:udX%23ZQKuKHA.5940(a)TK2MSFTNGP02.phx.gbl... >>> Did you check the things I asked/suggested in my last reply? >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> >>> "Jeff" <it_consultant1(a)hotmail.com.NOSPAM> wrote in message >>> news:#G4dn4GuKHA.1796(a)TK2MSFTNGP02.phx.gbl... >>>> That method gives my name, not the database user I try to connect to db >>>> with. >>>> >>>> So the string is "Jeff on PC3" (I called my computer PC3, not a very >>>> original name) >>>> >>>> >>>> >> >>
From: Tibor Karaszi on 2 Mar 2010 13:35 I suggest you specify a valid login as the owner, using the ALTER AUTHORIZATION command (documented in Books Online). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jeff" <it_consultant1(a)hotmail.com.NOSPAM> wrote in message news:uaQhkQiuKHA.4636(a)TK2MSFTNGP06.phx.gbl... > I made a search from sys.databases, there I found owner_sid of the > database. Then I checked in server.princials and I didn't find the sid in > the sid column. > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote > in message news:uSXvR0huKHA.3428(a)TK2MSFTNGP06.phx.gbl... >>I would still check if the database owner is orphaned. Check sys.databases >>and see whether the sid for the owner is who it should be. Then do a >>SELECT from sys.server_principals and inside your "strange" database >>sys.database_principals. Check the dbo user, that this has the expected >>SID number to match in sys.server_principals. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "Jeff" <it_consultant1(a)hotmail.com.NOSPAM> wrote in message >> news:OWADMbhuKHA.732(a)TK2MSFTNGP06.phx.gbl... >>> Yes, I've checked it, except the SID thing wasn't sure about how to do >>> it. >>> >>> But regarding orphaned users: I've created a dozens users (new database >>> login and create user which I assign to the new login)... All gives the >>> same error : Login failed for user... I've checked many times that >>> database, username etc is spelled correct >>> >>> I found this in the errorlog, maybe it can help find the error: >>> The SQL Network Interface library could not register the Service >>> Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. >>> Failure to register an SPN may cause integrated authentication to fall >>> back to NTLM instead of Kerberos. This is an informational message. >>> Further action is only required if Kerberos authentication is required >>> by authentication policies. >>> >>> And this is from errorlog aswell: >>> Server is listening on [ 'any' <ipv4> 4344] >>> >>> any suggestions? >>> >>> >>> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote >>> in message news:udX%23ZQKuKHA.5940(a)TK2MSFTNGP02.phx.gbl... >>>> Did you check the things I asked/suggested in my last reply? >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://sqlblog.com/blogs/tibor_karaszi >>>> >>>> >>>> >>>> "Jeff" <it_consultant1(a)hotmail.com.NOSPAM> wrote in message >>>> news:#G4dn4GuKHA.1796(a)TK2MSFTNGP02.phx.gbl... >>>>> That method gives my name, not the database user I try to connect to >>>>> db with. >>>>> >>>>> So the string is "Jeff on PC3" (I called my computer PC3, not a very >>>>> original name) >>>>> >>>>> >>>>> >>> >>> > >
From: Jeff on 3 Mar 2010 05:43 I'm trying this syntax, but somehow it doesn't work: alter authorization on database :: myDB to myLogin wonder if its' wrong of me to use database in the above script, maybe schema instead. Maybe I should use something else than myUser also.. I'll keep reading in the documentation "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:%23Myi1bjuKHA.5812(a)TK2MSFTNGP02.phx.gbl... >I suggest you specify a valid login as the owner, using the ALTER >AUTHORIZATION command (documented in Books Online). > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Jeff" <it_consultant1(a)hotmail.com.NOSPAM> wrote in message > news:uaQhkQiuKHA.4636(a)TK2MSFTNGP06.phx.gbl... >> I made a search from sys.databases, there I found owner_sid of the >> database. Then I checked in server.princials and I didn't find the sid in >> the sid column. >> >> >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote >> in message news:uSXvR0huKHA.3428(a)TK2MSFTNGP06.phx.gbl... >>>I would still check if the database owner is orphaned. Check >>>sys.databases and see whether the sid for the owner is who it should be. >>>Then do a SELECT from sys.server_principals and inside your "strange" >>>database sys.database_principals. Check the dbo user, that this has the >>>expected SID number to match in sys.server_principals. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> >>> "Jeff" <it_consultant1(a)hotmail.com.NOSPAM> wrote in message >>> news:OWADMbhuKHA.732(a)TK2MSFTNGP06.phx.gbl... >>>> Yes, I've checked it, except the SID thing wasn't sure about how to do >>>> it. >>>> >>>> But regarding orphaned users: I've created a dozens users (new database >>>> login and create user which I assign to the new login)... All gives the >>>> same error : Login failed for user... I've checked many times that >>>> database, username etc is spelled correct >>>> >>>> I found this in the errorlog, maybe it can help find the error: >>>> The SQL Network Interface library could not register the Service >>>> Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: >>>> 3. Failure to register an SPN may cause integrated authentication to >>>> fall back to NTLM instead of Kerberos. This is an informational >>>> message. Further action is only required if Kerberos authentication is >>>> required by authentication policies. >>>> >>>> And this is from errorlog aswell: >>>> Server is listening on [ 'any' <ipv4> 4344] >>>> >>>> any suggestions? >>>> >>>> >>>> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> >>>> wrote in message news:udX%23ZQKuKHA.5940(a)TK2MSFTNGP02.phx.gbl... >>>>> Did you check the things I asked/suggested in my last reply? >>>>> >>>>> -- >>>>> Tibor Karaszi, SQL Server MVP >>>>> http://www.karaszi.com/sqlserver/default.asp >>>>> http://sqlblog.com/blogs/tibor_karaszi >>>>> >>>>> >>>>> >>>>> "Jeff" <it_consultant1(a)hotmail.com.NOSPAM> wrote in message >>>>> news:#G4dn4GuKHA.1796(a)TK2MSFTNGP02.phx.gbl... >>>>>> That method gives my name, not the database user I try to connect to >>>>>> db with. >>>>>> >>>>>> So the string is "Jeff on PC3" (I called my computer PC3, not a very >>>>>> original name) >>>>>> >>>>>> >>>>>> >>>> >>>> >> >>
From: sloan on 5 Mar 2010 13:02
So what does your connection string look like? Are you using integrated security? Or are you specifying a sql authentication username and password? .............. Because if you're using integrated security (also called windows authentication)...................then your LOGIN and dbUsers have to reflect "Jeff" ............... Or you need to switch to sql authentication. NOTE: Sql Authentication isn't "on" be default. You either need to install it that way, or activate it later. .......... http://www.google.com/#hl=en&source=hp&q=sql+authentication+vs+windows+authentication&aq=2&aqi=g10&aql=&oq=sql+auth&fp=c26c79a56c95bda8 http://www.google.com/#hl=en&q=sql+authentication+how+to+activate&aq=f&aqi=&aql=&oq=&fp=c26c79a56c95bda8 "Jeff" <it_consultant1(a)hotmail.com.NOSPAM> wrote in message news:%23G4dn4GuKHA.1796(a)TK2MSFTNGP02.phx.gbl... > That method gives my name, not the database user I try to connect to db > with. > > So the string is "Jeff on PC3" (I called my computer PC3, not a very > original name) > > > |