Login failed for User SA Error 18456 : sql, 18456, login, failed, user

May 17, 2008 12:57pm pdt

1. chapmandew 1,139,490
2. angelIII 656,012
3. aneeshat... 347,059
4. ee_rlee 305,823
5. dtodd 190,705
6. brejk 170,605
7. ptjcb 151,519
8. mark_wills 146,165
9. PFrog 98,658
10. acperkins 86,575
11. dqmq 75,630
12. dportas 74,146
13. momi_sabag 71,940
14. maradam 71,186
15. Thomasian 70,100

1. angelIII 1,936,203
2. chapmandew 1,139,490
3. aneeshat... 912,164
4. ptjcb 515,911
5. Yveau 505,473
6. dtodd 401,710
7. PFrog 368,686
8. dqmq 346,978
9. imitchie 324,100
10. ee_rlee 305,823
11. appari 277,897
12. Lowfatsp... 220,339
13. rboyd56 192,515
14. SQL_SERV... 183,215
15. acperkins 181,914

05.29.2007 at 11:50PM PDT, ID: 22601391

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

7.6

Zones: SQL Server 2005, MS SQL Server

Tags: sql, 18456, login, failed, user

Hi there,

One of our engineers has installed SQL 2005 Express on a customers PC that uses XP SP2.

I am trying to set up a remote connection from our offices but a this time I cannot even logon to the
server using the SA account locally whilst using RDP to the customers site !

I can logon using windows authentication.

When I right click the server and then select the security tab SQL and Windows Authentication is ticked.
I've checked the connections tab an this allows Remote Connections.
I've looked at SA in the security tab and have set the password to what I want.

No effect.

Using the Config Manager I have disabled all protocols other than TCP/IP

When I restarted all the services and have tried to logon again but get the error message
Login Failed for User SA error 18465.

This is probably very simple but I cannot seem to spot what is wrong. Can anyone help

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: PaulEll

Solution Provided By: KarinLoos

Participating Experts: 3

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

05.30.2007 at 01:05AM PDT, ID: 19178039

Rank: Master

Qlemo:

According to http://msdn2.microsoft.com/en-us/library/ms131024.aspx this is a password problem (too long).

05.30.2007 at 01:23AM PDT, ID: 19178109

opho:

Hey PaulEll...

Actually, if I'm reading this accurately, Olemo transposed 18456 to 18465 in matching this up with the "password too long" error message.

Here's some info from an MSN staff person on debugging a login error:

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

The key is starting with the server log to find out what the Error State is, which will tell you whether SQL 2005 is having a problem with the user ID, password, or whatever.

--Steve

05.30.2007 at 01:43AM PDT, ID: 19178172

PaulEll:

Thanks for that.

I'll have a look at the logs and update you.

05.30.2007 at 02:28AM PDT, ID: 19178313

Rank: Master

Qlemo:

Oops

05.30.2007 at 02:33AM PDT, ID: 19178323

Rank: Guru

KarinLoos:

Per: http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

SQL Browser Service

In general when a client connects remotely to a SQL Server named instance the client does not know the port that the instance is listening on(you can work around this by configuring SQL Server named instances to listen on a specific port and then specifying that port in the connection string in the format <servername>,<port number>)

So when an application attempts to connect to a named instance in the form <servername>\<instancename> the client connection needs to be directed to the correct port. In SQL Server 2000 one of the running services had a built in listener that received the named instance connection requests and redirected them appropriately.

In SQL Server 2005 this functionality has been moved to a dedicated listener service, that service is SQLBrowser.

SQLBrowser also performs another service, that of SQL Server Discovery, its common in many UIs from Microsoft and others to have the ability to browse for instances of SQL Server running locally or remotely, again in SQL Server 2000 this was handled by one of the running instances of SQL Server, in SQL Server 2005 this is handled by the SQLBrowser Service.

Thus the SQLBrowser Service must be started to be able to discover any instance on the machine, and also to connect to named instances through protocols other than shared memory. If network protocols are not enabled via the setup switch then the browser service is not set to autostart.

05.30.2007 at 05:42AM PDT, ID: 19179345

PaulEll:

HI there,

Thanks for the posts.

Karin - I followed the link and can use the sqlcmd lines to connect but I still cannot connect using the server manager using SQL authentication via user SA. I can connect using windows authentication.

SQL Server (SQLExpress) is running
SQL Server Browser is running
SQL Full Text is running

The SQL Error log indicates that I have the wrong password - but as I set this and have changed this
(just to make sure) I can't see that this is the problem.

There is a line in the error log which may be important:

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.

I've set the surface error manager to allow remote connections via TCP/IP

The Config Manager is set as follows

IP1
Active: yes
Enabled: no
IP Address 192.168.1.101
TCP Dynamic Port: 0
TCP Port: Left Blank

IP2
Active Yes
Enabled No
IP Address 127.0.0.1
TCP Dynamic Port 0
TCP Port Left Blank

IPAll
TCP Dynamic Ports 1725
TCP Port left blank

Client Protocols:
default 1433
Enabled Yes
Keep Alive 30000
Keep Alive interval 1000

Full Log:

2007-05-30 12:01:24.18 Server Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
      Mar 23 2007 16:28:52
      Copyright (c) 1988-2005 Microsoft Corporation
      Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-05-30 12:01:24.18 Server (c) 2005 Microsoft Corporation.
2007-05-30 12:01:24.18 Server All rights reserved.
2007-05-30 12:01:24.18 Server Server process ID is 1188.
2007-05-30 12:01:24.18 Server Authentication mode is MIXED.
2007-05-30 12:01:24.18 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-05-30 12:01:24.18 Server This instance of SQL Server last reported using a process ID of 1736 at 5/30/2007 12:01:22 PM (local) 5/30/2007 11:01:22 AM (UTC). This is an informational message only; no user action is required.
2007-05-30 12:01:24.18 Server Registry startup parameters:
2007-05-30 12:01:24.18 Server       -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-05-30 12:01:24.18 Server       -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-05-30 12:01:24.18 Server       -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-05-30 12:01:24.18 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-05-30 12:01:24.18 Server Detected 2 CPUs. This is an informational message; no user action is required.
2007-05-30 12:01:24.78 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.
2007-05-30 12:01:24.79 Server Database mirroring has been enabled on this instance of SQL Server.
2007-05-30 12:01:24.81 spid5s Starting up database 'master'.
2007-05-30 12:01:25.07 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2007-05-30 12:01:25.24 spid5s SQL Trace ID 1 was started by login "sa".
2007-05-30 12:01:25.28 spid5s Starting up database 'mssqlsystemresource'.
2007-05-30 12:01:25.32 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-05-30 12:01:25.87 spid8s Starting up database 'model'.
2007-05-30 12:01:25.89 spid5s Server name is 'GAV-243028B7279\SQLEXPRESS'. This is an informational message only. No user action is required.
2007-05-30 12:01:26.14 Server A self-generated certificate was successfully loaded for encryption.
2007-05-30 12:01:26.15 Server Server is listening on [ 'any' <ipv4> 1725].
2007-05-30 12:01:26.15 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2007-05-30 12:01:26.15 Server 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.
2007-05-30 12:01:26.15 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2007-05-30 12:01:26.20 spid5s Starting up database 'msdb'.
2007-05-30 12:01:26.31 spid8s Clearing tempdb database.
2007-05-30 12:01:28.10 spid8s Starting up database 'tempdb'.
2007-05-30 12:01:28.59 spid5s Recovery is complete. This is an informational message only. No user action is required.
2007-05-30 12:01:28.59 spid11s The Service Broker protocol transport is disabled or not configured.
2007-05-30 12:01:28.59 spid11s The Database Mirroring protocol transport is disabled or not configured.
2007-05-30 12:01:28.70 spid11s Service Broker manager has started.
2007-05-30 12:05:24.79 Server The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.
2007-05-30 12:17:13.26 Server Server resumed execution after being idle 913 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2007-05-30 12:17:45.17 Logon Error: 18456, Severity: 14, State: 8.
2007-05-30 12:17:45.17 Logon Login failed for user 'sa'. [CLIENT: 192.168.1.101]

Thanks for your help

05.30.2007 at 07:46AM PDT, ID: 19180453

Rank: Guru

KarinLoos:

re this message
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3.....
see this link : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=73901&SiteID=1
Its not the problem in your case

Just a thought, when trying to connect as SA, take note Sql2005 passwords are case sensitive

Accepted Solution

05.30.2007 at 08:19AM PDT, ID: 19180743

PaulEll:

Thanks for that.

I'll go back an change the password again and make sure that it is lowercase.

I'm running out of ideas though.

20080236-EE-VQP-29