I have reviewed the blog posting "Understanding "login failed" (Error
18456) error messages in SQL Server 2005" at
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
2007-03-21 11:49:14.55 Logon Error: 18456, Severity: 14, State: 8.
2007-03-21 11:49:14.55 Logon Login failed for user 'USERNAMEOBSCURED'.
[CLIENT: 192.168.183.144]
According to the blog, this indicates that the password is invalid, but I
have reset it 5 times, making sure the password is the same at both ends.
This username, USERNAMEOBSCURED, is a SQL Server authentication type user,
and I have verfied that Enforce password policy, expiration, must change
password are all unchecked and the account is not set to locked out.
The ODBC client is another Windows Server 2003 SP1 computer in the same
workgroup as the SQL 2005 SP1 server. I am using the Microsoft SQL Server
ODBC Driver Version 03.86.1830 via TCP/IP.
Since I don't have that much experence running SQL server, which and
what database permissions should USERNAMEOBSCURED be given? If I want to
create a new username to test it, how should that be done for SQL Server
authentication? If these are in the books online, please point me to the
right location.
Hi,
I understand that you encountered the error:
Login failed for user 'USERNAMEOBSCURED'
When you remotely use ODBC driver to connect to your SQL 2005 instance.
If I have misunderstood, please let me know.
For troubleshooting the login errors, you may refer to:
Troubleshooting: Login failed for user 'x'
http://msdn2.microsoft.com/en-us/library/ms366351.aspx
How to: Change Server Authentication Mode
http://msdn2.microsoft.com/en-us/library/ms188670.aspx
Though the error state indicates that it is a password error, I recommend
that also check the following and let me know the results:
1. Check if you can log on your SQL Server locally with the login and
password;
2. Open SQL Server Configuration Manager to see if the TCP/IP protocol has
been enabled;
3. Open SQL Server Surface Area Configuration, click Surface Area
Configuration for Services and Connections to see if Local and remote
connections is selected under the Remote Connections.
4. If the issue persists, try creating a new login to see if this issue
persists;
5. Run "osql /S<server name> /U<login id> /P<password>" from command line
on your client computer to check if you can connect to your SQL Server;
6. Disable the firewall on your SQL Server computer and run "netstat -nba
>C:\serviceslist.log" to check which TCP port has been listened by your SQL
Server service (sqlservr.exe); then run "telnet <server name> <TCP port>"
from command on the client computer to check if you can log on the port.
7. Run "cliconfg.exe" from command line on the client computer, switch to
the Alias tab, add an alias with TCP/IP protocol for the SQL instance.
Please note that fill in the correct Tcp port and server name (the SQL
Server computer name) in the input fields. Then run the step 2 again.
If all above methods still cannot make it work, please use NetMon to
monitor the network stream by filter the TCP protocal and port.
You may refer to:
Microsoft Network Monitor 3
http://www.microsoft.com/downloads/details.aspx?familyid=AA8BE06D-4A6A-4B69-
B861-2043B665CB53&displaylang=en
How To Capture TCP and HTTP Data Frames Using Network Monitor and MSXML
HTTP Code
http://support.microsoft.com/kb/295405
How to capture network traffic with Network Monitor
http://support.microsoft.com/kb/148942
Troubleshooting by Using Network Monitor
http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/intwork/
inbc_ias_ykqb.mspx?mfr=true
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Using suggestion 4 by creating a new user USERNAMEOBSCUREDTEST and
setting the User Mapping set to DATABASENAMEOBSCURED database under Default
Schema dbo is proving to be a temporary workaround. I am consulting with
the 3rd party creator of the database to try and understand why the username
is already in the database but is not mapped according to SQL 2005. I am
wondering if this is due to the fact that the database was not created fresh
in SQL 2005, it was imported from an existing SQL 7 database from another
machine. Apparently USERNAMEOBSCURED is not mapped to any database according
to the view in Microsoft SQL Server Management Studio, and when I attempt to
map the username to DATABASENAMEOBSCURED with the default Database role
membership of public I get this error:
"TITLE: Microsoft SQL Server Management Studio
Create failed for User 'USERNAMEOBSCURED'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+User&LinkId=20476[/url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
User, group, or role 'USERNAMEOBSCURED' already exists in the current
database. (Microsoft SQL Server, Error: 15023)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=15023&LinkId=20476[ /url]
BUTTONS:
OK
--"
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:ic0m3bFbHHA.1028@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> I understand that you encountered the error:
> Login failed for user 'USERNAMEOBSCURED'
> When you remotely use ODBC driver to connect to your SQL 2005 instance.
> If I have misunderstood, please let me know.
> For troubleshooting the login errors, you may refer to:
> Troubleshooting: Login failed for user 'x'
> http://msdn2.microsoft.com/en-us/library/ms366351.aspx
> How to: Change Server Authentication Mode
> http://msdn2.microsoft.com/en-us/library/ms188670.aspx
> Though the error state indicates that it is a password error, I recommend
> that also check the following and let me know the results:
> 1. Check if you can log on your SQL Server locally with the login and
> password;
> 2. Open SQL Server Configuration Manager to see if the TCP/IP protocol has
> been enabled;
> 3. Open SQL Server Surface Area Configuration, click Surface Area
> Configuration for Services and Connections to see if Local and remote
> connections is selected under the Remote Connections.
> 4. If the issue persists, try creating a new login to see if this issue
> persists;
> 5. Run "osql /S<server name> /U<login id> /P<password>" from command line
> on your client computer to check if you can connect to your SQL Server;
> 6. Disable the firewall on your SQL Server computer and run "netstat -nba
> Server service (sqlservr.exe); then run "telnet <server name> <TCP port>"
> from command on the client computer to check if you can log on the port.
> 7. Run "cliconfg.exe" from command line on the client computer, switch to
> the Alias tab, add an alias with TCP/IP protocol for the SQL instance.
> Please note that fill in the correct Tcp port and server name (the SQL
> Server computer name) in the input fields. Then run the step 2 again.
> If all above methods still cannot make it work, please use NetMon to
> monitor the network stream by filter the TCP protocal and port.
> You may refer to:
> Microsoft Network Monitor 3
> http://www.microsoft.com/downloads/details.aspx?familyid=AA8BE06D-4A6A-4B69-
> B861-2043B665CB53&displaylang=en
> How To Capture TCP and HTTP Data Frames Using Network Monitor and MSXML
> HTTP Code
> http://support.microsoft.com/kb/295405
> How to capture network traffic with Network Monitor
> http://support.microsoft.com/kb/148942
> Troubleshooting by Using Network Monitor
> http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/intwork/
> inbc_ias_ykqb.mspx?mfr=true
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
>
>
>
>
>
>
|||Hi,
Thanks for your response.
Since your database is migrated from SQL 7.0, this issue is indeed
possible. Please try the tool mapsids.exe according to this article to see
if this issue persists:
How to resolve permission issues when you move a database between servers
that are running SQL Server
http://support.microsoft.com/kb/240872/EN-US/
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/kb/274188/
Hope this helps.
Best regards,
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Sorry for the long delay, we were able to fix the issue with the
duplicate users, I'll have to dig up my notes from two weeks ago to recall
the procedure we used. Sorry for not closing this issue sooner.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:Y6QkJsFcHHA.2060@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> I am interested in this issue. Would you mind letting me know the result
> of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
|||Hi,
Thanks for your updating and response. Please do not worry about it.
Anytime your posting back is welcome.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
