I wrote an application using Visual Studio 2005. This app requires a login that get that accesses SQL Express Data.
I have an XP Pro box set up as a server using DSL Modem and connected to that is a Wireless hub.
I used Network Wizard to set up a Home/Small Business Network on all the computers.
My laptop has XP Pro and has Visual Studio and that's where I wrote the application. It's connect to the wirless hub with a cat 5 cable.
The other laptop has XP Home Edition and is in another room is connected with wirless only.
I set up a user called user1 in SQLExpress with a password. I set it up using MIXED MODE. (SQL server and Windows authentication mode)
Connection String: myCn.ConnectionString = "Provider=SQLOLEDB;Data Source=ServerBox\SQLEXPRESS;Initial Catalog=db1;User ID=user1;Password=12345"
I turned off Simple Filesharing on the XP Pro box (Server).
In User Accounts XP...Guest account is turned OFF
When I run the applicaton I wrote from MY laptop (connected with cat 5 or Wireless), the application logs me in and I see all the data...It works
BUT when I run the appication from the other laptop (connected with wirless), I get an error : "Login Failed....ServerBox\Guest".
I'M STUMPED...DON'T KNOW WHAT TO DO ...CAN ANY HELP......
The first thought I had was I wonder if the 2nd laptop has SQL Server database drivers installed... I don't know if XP home installs them by default or not...|||No..the second laptop dosn't have sql drivers installed. Also the 2nd laptop has .net framework 1.1 not .net framework 2, not sure if that matters.|||Can you verify the connection string used on the failing laptop?
The error message indicates that WIndows authentication was used even though the connection string above uses SQL Server authentication (by specifying User ID).
There should be a "login failed" message also in the SQL Express application event log (use Event Viewer to view), which contains a state number which may provide additional insights - can you paste that here omitting any confidential information (IP addresses, etc.)?
|||
This is the error log...I changed the servername and IP addresses.....
The laptop in question is using XP Home Edition
2006-10-09 06:49:38.81 Server Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)
2006-10-09 06:49:38.89 Server (c) 2005 Microsoft Corporation.
2006-10-09 06:49:38.89 Server All rights reserved.
2006-10-09 06:49:38.89 Server Server process ID is 1896.
2006-10-09 06:49:38.89 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2006-10-09 06:49:38.90 Server This instance of SQL Server last reported using a process ID of 1928 at 10/8/2006 3:28:44 PM (local) 10/8/2006 7:28:44 PM (UTC). This is an informational message only; no user action is required.
2006-10-09 06:49:38.90 Server Registry startup parameters:
2006-10-09 06:49:38.94 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-10-09 06:49:38.94 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-10-09 06:49:38.94 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-10-09 06:49:39.05 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-10-09 06:49:39.05 Server Detected 1 CPUs. This is an informational message; no user action is required.
2006-10-09 06:49:39.90 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.
2006-10-09 06:49:40.71 Server Database mirroring has been enabled on this instance of SQL Server.
2006-10-09 06:49:40.86 spid5s Starting up database 'master'.
2006-10-09 06:49:41.18 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-10-09 06:49:41.51 spid5s SQL Trace ID 1 was started by login "sa".
2006-10-09 06:49:41.58 spid5s Starting up database 'mssqlsystemresource'.
2006-10-09 06:49:41.61 spid5s The resource database build version is 9.00.2047. This is an informational message only. No user action is required.
2006-10-09 06:49:42.02 spid8s Starting up database 'model'.
2006-10-09 06:49:42.04 spid5s Server name is 'MYSERVER\SQLEXPRESS'. This is an informational message only. No user action is required.
2006-10-09 06:49:42.04 spid5s Starting up database 'msdb'.
2006-10-09 06:49:42.58 Server A self-generated certificate was successfully loaded for encryption.
2006-10-09 06:49:43.16 spid8s Clearing tempdb database.
2006-10-09 06:49:44.65 spid8s Starting up database 'tempdb'.
2006-10-09 06:49:45.26 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-10-09 06:49:45.30 spid11s The Service Broker protocol transport is disabled or not configured.
2006-10-09 06:49:45.35 spid11s The Database Mirroring protocol transport is disabled or not configured.
2006-10-09 06:49:46.83 spid11s Service Broker manager has started.
2006-10-09 06:49:47.93 Server Server is listening on [ 'any' <ipv4> 1632].
2006-10-09 06:49:47.93 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2006-10-09 06:49:47.93 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2006-10-09 06:49:47.96 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.
2006-10-09 06:49:47.98 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. 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.
2006-10-09 06:49:47.98 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-10-09 07:03:44.08 Server Server resumed execution after being idle 807 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2006-10-09 07:03:59.40 spid51 Using 'xpstar90.dll' version '2005.90.2047' to execute extended stored procedure 'xp_enumerrorlogs'. This is an informational message only; no user action is required.
2006-10-09 07:44:23.82 Server Server resumed execution after being idle 1499 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2006-10-09 07:44:24.29 spid51 Starting up database 'db1'.
2006-10-09 08:36:00.42 Server Server resumed execution after being idle 2171 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2006-10-09 08:36:08.78 Logon Error: 18456, Severity: 14, State: 16.
2006-10-09 08:36:08.78 Logon Login failed for user 'MYSERVER\Guest'. [CLIENT: 100.100.1.100]
2006-10-09 08:47:50.96 spid52 Using 'xplog70.dll' version '2005.90.2047' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2006-10-09 08:50:50.02 Logon Error: 18456, Severity: 14, State: 16.
2006-10-09 08:50:50.02 Logon Login failed for user 'MYSERVER\Guest'. [CLIENT: 100.100.1.100]
2006-10-09 09:28:40.91 Server Server resumed execution after being idle 45 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2006-10-09 09:55:25.09 Logon Error: 17806, Severity: 20, State: 2.
2006-10-09 09:55:25.09 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 100.100.1.100]
2006-10-09 09:55:25.09 Logon Error: 18452, Severity: 14, State: 1.
2006-10-09 09:55:25.09 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 100.100.1.100]
2006-10-09 10:26:09.32 Server Server resumed execution after being idle 737 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2006-10-09 10:35:36.64 Logon Error: 18456, Severity: 14, State: 16.
2006-10-09 10:35:36.64 Logon Login failed for user 'MYSERVER\Guest'. [CLIENT: 100.100.1.100]
2006-10-09 10:37:21.16 Logon Error: 18456, Severity: 14, State: 16.
2006-10-09 10:37:21.16 Logon Login failed for user 'MYSERVER\Guest'. [CLIENT: 100.100.1.100]
2006-10-09 10:38:45.95 Logon Error: 18456, Severity: 14, State: 16.
2006-10-09 10:38:45.95 Logon Login failed for user 'MYSERVER\Guest'. [CLIENT: 100.100.1.100]
2006-10-09 11:30:57.70 Logon Error: 18456, Severity: 14, State: 16.
2006-10-09 11:30:57.70 Logon Login failed for user 'MYSERVER\Guest'. [CLIENT: 100.100.1.100]
2006-10-09 11:35:06.42 Logon Error: 18456, Severity: 14, State: 16.
2006-10-09 11:35:06.42 Logon Login failed for user 'MYSERVER\Guest'. [CLIENT: 100.100.1.100]
2006-10-09 11:37:52.79 Logon Error: 18456, Severity: 14, State: 16.
2006-10-09 11:37:52.79 Logon Login failed for user 'MYSERVER\Guest'. [CLIENT: 100.100.1.100]
2006-10-09 12:02:28.78 Server Server resumed execution after being idle 563 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2006-10-09 12:02:30.29 Logon Error: 18456, Severity: 14, State: 16.
2006-10-09 12:02:30.29 Logon Login failed for user 'MYSERVER\Guest'. [CLIENT: 100.100.1.100]
2006-10-09 12:24:35.74 Server Server resumed execution after being idle 417 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2006-10-09 12:42:25.36 Server Server resumed execution after being idle 144 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2006-10-09 16:48:01.64 Server Server resumed execution after being idle 13826 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2006-10-09 16:49:19.98 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
2006-10-09 16:49:27.51 spid11s Service Broker manager has shut down.
2006-10-09 16:49:27.63 spid11s Error: 17054, Severity: 16, State: 1.
2006-10-09 16:49:27.63 spid11s The current event was not reported to the Windows Events log. Operating system error = 1717(The interface is unknown.). You may need to clear the Windows Events log if it is full.
2006-10-09 16:49:28.71 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
|||
Are you able to verify the connection string actually used on the failing machine?
Error messages that specify the user name in the form 'MYSERVER\Guest' typically indicate the use of Windows ("Integrated") authentication?
If you want to use Windows authentication, and your computers are in a workgroup (i.e. they are not joined to a domain) then you must create a Windows account to access the SQL Server with the same name and same password on both the SQL Server machine and every client machine that connects to the SQL Server, and use that account when making database connections.
|||When you say Windows Account, do you mean "User Accounts" on the Client and Server Machines?|||
Yes, "User Accounts" in the sense of the OS (Windows), not SQL Server.
|||
Thanks...I created a user account in the xp pro serverbox identical to the home edition laptop of the user.
I set up the user in sqlexpress........SERVER\laptopuser...Windows Authentication...default Database "db1".. The only thing is that the user never created a password in his home edition xp..So I left the Password BLANK. When he starts his computer there is NO login screen...he just goes to his workspace. Is that a problem, is a password needed.....?.
Anyway when I try to login from the users laptop I get error....cannot open db1.....So it looks like its loggin in to sqlexpress, but can't use db1 database. He is listed under both..Server Security and db1 security....db1
|||Your initial tact to use SQL Server Authentication seemed like a perfectly adequate solution for your situation (and far more manageable). It's not clear to me how your connection is mapping into Windows Authentication mode (this would have "Trusted_Connection=yes" in the connection string...note the all important underscore, btw)...getting to the bottom of this may help shed some light on why your app is failing to connect.
Can you verify the connection string in your client app? Given your comment about versions of the .Net Framework that were installed, I'm assuming that this is a managed app...if that's correct, you can probably find the connection string by looking for the creation of a new SqlConnection in your code (the connection string would be the parameter to the constructor).
With regards to connecting using a blank password, it should, technically speaking, work fine. However, in terms of allowing access to your database, I would recommend against it.
|||The settings for SQL Server Authentication are... Connections strings:
myCn.ConnectionString = "Provider=SQLOLEDB;Data Source=ServerBox\SQLEXPRESS;Initial Catalog=db1;User ID=user1;Password=12345"
The setting for Windows Authentication are... Connection string:
myCn.ConnectionString = "Provider=SQLOLEDB;Data Source=ServerBox\SQLEXPRESS;Initial Catalog=db1;Integrated Security=SSPI
I change the Application "Setting" to reflect the connection string I will be using, then re-complie the app.
|||Which of these two connection strings works/fails on which client machine?|||Both fail on 1st client machine (xp home edition)
Both work on 2nd client machine (xp pro)
Also I just wanted to say that in my vb app, in debug..I notice that I get a connection to the db, but when the program continues....and starts reading the "TableAdapter.Fill" I get the error "login Failed for user1.
I Set up the user1 in SQLexpress for SQL Authentication with the default db as 'db1'..I checkd permissions in Instance properties and effective permissions show 'Connect SQL" and 'View Any Database'.
I checked the xsd(xml) and in debug mode when I select to "Preview Data" I ineter username and password and the data comes up. My Settings in the application are set as I stated to SQL Authentication.
Securables show nothing under db1 security and server security. I don't know what securables are...When I tried to add some, then save...and go back to look at them...they are gone ?
I must be missing something in SQL express Users and Logins...but I don't know what.
I'm at a loss ......!!!!!
What is the state of the Error 18456 in SQL Server ERROROLOG when it says "Login Failed for user1"? Is it 16, as it was for MYSERVER\Guest?
State 16 is explained in this blog post:
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
# re: Understanding "login failed" (Error 18456) error messages in SQL Server 2005
Tuesday, March 14, 2006 2:24 AM by Matt Neerincx [MSFT]
State=16 means that the incoming user does not have permissions to log into the target database. So for example say you create a user FOO and set FOO's default database to master, but FOO does not have permissions to log into master.
This can also happen if for example the default database for user FOO is not online (for example the database is marked suspect).
So to check on this theory, try logging the user into some other database and then try using the USE DATABASE command to switch to the target database, you will get a better error message as to the root cause of the failure.
|||
I get Error 18456, Severity 14, State: 8
From what I can gather State 8 means incorrect password. But I double checked and the password I'm using IS CORRECT. ?
The password I'm using is 6 characters all lowercase
Like i said, I connect to the server, but when it comes to accessing the database 'db1', I get the error.
Effective Permissions show: CONNECT SQL and VIEW ANY DATABASE
No comments:
Post a Comment