Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 After Reinstall, STILL Unable to Log In

Author  Topic 

Wylbur
Starting Member

29 Posts
Posted - 2009-01-26 : 21:24:32

Hi again all;

I've just completed a re-install of SQL 2005, and I am STILL unable
to log into the server - even with an administration account.

Could these have something to do with it?:
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
2009-01-26 20:02:03.37 spid12s The Service Broker protocol transport is disabled or not configured.
2009-01-26 20:02:03.37 spid12s The Database Mirroring protocol transport is disabled or not configured.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



What follows is the complete log:
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
2009-01-26 20:01:46.70 Server Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 6.0 (Build 6001: Service Pack 1)

2009-01-26 20:01:47.21 Server (c) 2005 Microsoft Corporation.
2009-01-26 20:01:47.21 Server All rights reserved.
2009-01-26 20:01:47.21 Server Server process ID is 1452.
2009-01-26 20:01:47.21 Server Authentication mode is WINDOWS-ONLY.
2009-01-26 20:01:47.29 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2009-01-26 20:01:47.29 Server This instance of SQL Server last reported using a process ID of 5128 at 1/26/2009 7:59:16 PM (local) 1/27/2009 1:59:16 AM (UTC). This is an informational message only; no user action is required.
2009-01-26 20:01:47.29 Server Registry startup parameters:
2009-01-26 20:01:47.43 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2009-01-26 20:01:47.43 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2009-01-26 20:01:47.43 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2009-01-26 20:01:47.70 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2009-01-26 20:01:47.70 Server Detected 2 CPUs. This is an informational message; no user action is required.
2009-01-26 20:01:53.15 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2009-01-26 20:01:53.24 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-01-26 20:01:55.06 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
2009-01-26 20:01:55.49 Server Database mirroring has been enabled on this instance of SQL Server.
2009-01-26 20:01:55.52 spid5s Starting up database 'master'.
2009-01-26 20:01:56.37 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2009-01-26 20:01:56.55 spid5s SQL Trace ID 1 was started by login "sa".
2009-01-26 20:01:56.62 spid5s Starting up database 'mssqlsystemresource'.
2009-01-26 20:01:56.82 spid5s The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
2009-01-26 20:01:57.44 spid5s Server name is 'JOE\SQLINSTANCE01'. This is an informational message only. No user action is required.
2009-01-26 20:01:57.46 spid9s Starting up database 'model'.
2009-01-26 20:01:58.74 spid9s Clearing tempdb database.
2009-01-26 20:01:58.84 Server A self-generated certificate was successfully loaded for encryption.
2009-01-26 20:01:58.87 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLINSTANCE01 ].
2009-01-26 20:01:58.87 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLINSTANCE01\sql\query ].
2009-01-26 20:01:58.87 Server Server is listening on [ ::1 <ipv6> 49551].
2009-01-26 20:01:58.88 Server Server is listening on [ 127.0.0.1 <ipv4> 49551].
2009-01-26 20:01:58.88 Server Dedicated admin connection support was established for listening locally on port 49551.
2009-01-26 20:01:58.89 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2009-01-26 20:02:00.36 spid12s Starting up database 'msdb'.
2009-01-26 20:02:00.36 spid14s Starting up database 'AdventureWorks'.
2009-01-26 20:02:00.36 spid13s Starting up database 'AdventureWorksDW'.
2009-01-26 20:02:02.50 spid9s Starting up database 'tempdb'.
2009-01-26 20:02:03.32 spid5s Recovery is complete. This is an informational message only. No user action is required.
2009-01-26 20:02:03.37 spid12s The Service Broker protocol transport is disabled or not configured.
2009-01-26 20:02:03.37 spid12s The Database Mirroring protocol transport is disabled or not configured.
2009-01-26 20:02:03.39 spid12s Service Broker manager has started.
2009-01-26 20:02:06.34 spid51 Using 'xpsqlbot.dll' version '2005.90.4035' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2009-01-26 20:02:07.60 spid51 Using 'xpstar90.dll' version '2005.90.4035' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2009-01-26 20:02:08.47 spid51 Using 'xplog70.dll' version '2005.90.4035' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2009-01-26 20:08:15.65 Logon Error: 18456, Severity: 14, State: 11.
2009-01-26 20:08:15.65 Logon Login failed for user 'JOE\SQL Joe'. [CLIENT: <local machine>]
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-


I'm getting to be very discouraged with this.

I'm hoping that someone can help this poor fool out.

THANKS!!!




Wylbur
========================

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-26 : 22:01:09
What are you specifying when you are trying to connect?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Wylbur
Starting Member

29 Posts
Posted - 2009-01-26 : 23:20:30
quote:
Originally posted by tkizer

What are you specifying when you are trying to connect?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



HI Tara;

Server Type : Database Engine
Server Name : JOE\SQLINSTANCE01
Authentication : Windows Authentication

Is there something that I'm missing here?

THANKS!!!





Wylbur
========================
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-27 : 00:00:25
That looks good so far. And from there you are getting "login failed", right? I just want to make sure that the error that you showed us from the error log is also the error that you are seeing in Management Studio.

During the install process, did you specify mixed mode authentication or did you keep it at the default of Windows only? If you did select mixed mode authentication, have you tried connecting with the sa password? Also, have you tried creating a new Windows user, granting that local admin, logging into Windows with that account, and then attempting to login into SQL with that account?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Wylbur
Starting Member

29 Posts
Posted - 2009-01-27 : 00:46:09
quote:
Originally posted by tkizer

That looks good so far. And from there you are getting "login failed", right? I just want to make sure that the error that you showed us from the error log is also the error that you are seeing in Management Studio.

During the install process, did you specify mixed mode authentication or did you keep it at the default of Windows only? If you did select mixed mode authentication, have you tried connecting with the sa password? Also, have you tried creating a new Windows user, granting that local admin, logging into Windows with that account, and then attempting to login into SQL with that account?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog




Hi Tara;

quote:

That looks good so far. And from there you are getting "login failed", right?


Yes.


quote:

During the install process, did you specify mixed mode authentication or did you keep it at the default of Windows only?


Windows authentication.


quote:

If you did select mixed mode authentication, have you tried connecting with the sa password?


I'm beginning to wonder if I would've been better off with mixed mode.
Is there any way to change the mode without having to perform another reinstall?


quote:

Also, have you tried creating a new Windows user, granting that local admin, logging into Windows with that account, and then attempting to login into SQL with that account?


I just got through trying that - no good - same problem.



THANKS!!!





Wylbur
========================
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-27 : 12:56:39
There isn't a way to change the mode unless you can get connected to the SQL Server. The setting gets changed inside the database server.

I'm clueless as to why this is happening and unfortunately I'm going to recommend that you reinstall and specify mixed mode authentication. Perhaps there is a Windows domain issue and so we need to verify if SQL authentication works.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Wylbur
Starting Member

29 Posts
Posted - 2009-01-27 : 13:55:25
quote:
Originally posted by tkizer

There isn't a way to change the mode unless you can get connected to the SQL Server. The setting gets changed inside the database server.

I'm clueless as to why this is happening and unfortunately I'm going to recommend that you reinstall and specify mixed mode authentication. Perhaps there is a Windows domain issue and so we need to verify if SQL authentication works.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



Hi Tara;

Is there no other way to change that setting without Management Studio?

I've sent a message to a team blog at Microsoft, explaining my
situation, and requesting assistance. I'll give them some time
to respond.

My MO on this sort of thing is to first post it to a few forums
(as I have with forums.asp.net before this one), while researching
the issue myself on the 'net (Google is my friend), then start bugging
people at Microsoft if I cannot get any answers from the forums.
(The gang on the ASP.NET development team are getting to be sick
of me.)

Do you know of anyone at Microsoft that might give me some love
and attention?

THANKS!!!




Wylbur
========================
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-27 : 14:09:33
If you can't get logged into SQL Server, then there is no way to switch to mixed mode authentication. The setting is stored in the master database, which means you must connect to it in order to change anything. The setting is not stored at the registry or similar level.

Hopefully someone at Microsoft will be able to help you fix why Windows authentication isn't working.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Wylbur
Starting Member

29 Posts
Posted - 2009-01-27 : 14:31:09
quote:
Originally posted by tkizer

If you can't get logged into SQL Server, then there is no way to switch to mixed mode authentication. The setting is stored in the master database, which means you must connect to it in order to change anything. The setting is not stored at the registry or similar level.

Hopefully someone at Microsoft will be able to help you fix why Windows authentication isn't working.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



Hi Tara;

Do you know which table(s) and which field(s) within that/those
table(s) would have to be changed?

... and what they would have to be changed to?

I'm giving some thought to writing something in my project for
resetting that/those value(s). From what I can ascertain, the
database server seems to be up and running - it's just that
management studio is unable to access.

... but that could be more trouble than it's worth.

I'm wondering if ASP.NET would have sufficient access privileges.

THANKS!!!




Wylbur
========================
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-27 : 15:45:21
I had been searching for it actually, and I was wrong about it being stored in the database. It is stored in the registry.

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.n\MSSQLServer

Update LoginMode to 2.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Wylbur
Starting Member

29 Posts
Posted - 2009-01-27 : 16:12:28
quote:
Originally posted by tkizer

I had been searching for it actually, and I was wrong about it being stored in the database. It is stored in the registry.

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.n\MSSQLServer

Update LoginMode to 2.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



HI Tara;

What should I give it for login name and password?

(No word yet from Microsoft.)

THANKS!!!




Wylbur
========================
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-27 : 16:36:47
Hmm, I'm not sure what the default sa password is. But sa is the only account that you'll have available on a new install. I supposed you could try a blank password.

I always install SQL Server as mixed mode authentication and provide an sa password during the installation, so I've never needed to figure this out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Wylbur
Starting Member

29 Posts
Posted - 2009-01-27 : 16:57:32
quote:
Originally posted by tkizer

Hmm, I'm not sure what the default sa password is. But sa is the only account that you'll have available on a new install. I supposed you could try a blank password.

I always install SQL Server as mixed mode authentication and provide an sa password during the installation, so I've never needed to figure this out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog




Hi Tara;

OK - I tried that (sa without a password), and this is the
error message that I've gotten:
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Connect to server

-------------------------------------------------------------------

Cannot connect to JOE/SQLINSTANCE01.

Additional information:

A connection was successfully established with the server, but then an error
occurred during the login process. (provider: Shared Memory Provider,
error: 0 - No process on the other end of the pipe.) (Microsoft SQL Server,
Error: 233)

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-


... so maybe the server is on the wrong pipe?
... or is it management studio that is on the wrong pipe?
... or is the error message misleading (and it's something else entirely)?


BTW: If it's so easy to reset the authentication mode within the registry,
then log in to sa with a blank password, then that seems to me like a HUGE
security vulnerability - yeah?


THANKS!!!





Wylbur
========================
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-27 : 16:59:56
Well I didn't say that a blank password would work, but it was worth a try. A blank password for the sa account was a real thing in SQL Server 2000 for MSDE.

Try connecting with TCP/IP: "tcp:ServerName\Instance,PortNumber"

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Wylbur
Starting Member

29 Posts
Posted - 2009-01-27 : 17:13:18
quote:
Originally posted by tkizer

Well I didn't say that a blank password would work, but it was worth a try. A blank password for the sa account was a real thing in SQL Server 2000 for MSDE.

Try connecting with TCP/IP: "tcp:ServerName\Instance,PortNumber"

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog




Hi Tara;


-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Try connecting with TCP/IP: "tcp:ServerName\Instance,PortNumber"
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

So I would enter into the server name field ...

Server Name : tcp:JOE\SQLINSTANCE01,xxx

How do I find the port number?

THANKS!!!




Wylbur
========================
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-27 : 18:58:20
According to your log, you are using 49551.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Wylbur
Starting Member

29 Posts
Posted - 2009-01-27 : 19:39:19
quote:
Originally posted by tkizer

According to your log, you are using 49551.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog




Hi Tara;


"According to your log, you are using 49551."

I should have thought to look at that.

... but it doesn't actually matter - it didn't get us anywhere:
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Connect to server

-------------------------------------------------------------------

Cannot connect to JOE/SQLINSTANCE01,49551.

Additional information:

An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: TCP Provider, error: 0 - No connection could be made because
the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



So remote connections are not allowed.

What else could I do wrong?

THANKS!!!




Wylbur
========================
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-27 : 19:56:23
That error is misleading. That's a connectivity error. You didn't use the right slash. It should be JOE\SQLINSTANCE01, not JOE/SQLINSTANCE01. If that doesn't work, read on:

To ensure connectivity, we'll need to run telnet.

On the client machine where you are getting these errors, run telnet.exe from Start..Run..cmd. Type in "JOE 49551" without the double quotes and hit enter. If you get a blank screen, then that's good. If you get an error, then you've got a network config issue somewhere. Let us know what you get.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Wylbur
Starting Member

29 Posts
Posted - 2009-01-27 : 20:26:00
quote:
Originally posted by tkizer

That error is misleading. That's a connectivity error. You didn't use the right slash. It should be JOE\SQLINSTANCE01, not JOE/SQLINSTANCE01. If that doesn't work, read on:

To ensure connectivity, we'll need to run telnet.

On the client machine where you are getting these errors, run telnet.exe from Start..Run..cmd. Type in "JOE 49551" without the double quotes and hit enter. If you get a blank screen, then that's good. If you get an error, then you've got a network config issue somewhere. Let us know what you get.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



Hi Tara;

Actually, I made a typo - it was a back-slash that I entered for SQL.
(I had to copy the window "by hand" as I could not mark it for
copy-paste.)

I don't believe that I have telnet anywhere on my system.

I'll have to download it from somewhere.

Any suggestions?


THANKS!!!




Wylbur
========================
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-01-27 : 20:28:59
It should be on your client machine. It comes with Windows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Wylbur
Starting Member

29 Posts
Posted - 2009-01-27 : 20:46:02
quote:
Originally posted by tkizer

It should be on your client machine. It comes with Windows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



Hi Tara;

It (telnet) mysteriously vanished from the laptop (Vista),
and I couldn't find it on my XP boxes either. However, it IS
on my Win 2k system. My only theory is that it was removed
from the other systems by my virus protection software.

Anyway ...

The attempt at making a connection failed.

THANKS!!!






Wylbur
========================
Go to Top of Page
  Previous Page&nsp;  Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site