Error: 18456, Severity: 14, State: 11 Valid login but server access failure

Hi

I am new to SQL server and I have been trying hard to make a client computer to remote connect to a SQL express database on host computer

I have a VB6 application that can connect to SQL server database LOCALLY without problem:

Connection String is:

my_connection.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyMushroom;Data Source=LAPTOP\SQLEXPRESS"

I have followed instruction on enabling remote connection function from this blog:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

I then try to run the same app from the client computer, it gives me:

Login failed for user 'LAPTOP\Guest'.

After looking up the web for solution, I found that I can test the connection from the HOST computer in this way:

C:\Documents and Settings\kit>sqlcmd -E -S laptop\sqlexpress
1>
2>

The test is successful

Now I run the same command on the CLIENT computer

C:\Documents and Settings\Kit>sqlcmd -E -S laptop\sqlexpress
Msg 18456, Level 14, State 1, Server LAPTOP\SQLEXPRESS, Line 1
Login failed for user 'LAPTOP\Guest'.

Now I can sure that from the client computer it cannot make a connection to it, then I look at the errorLog from my host computer

2006-08-13 21:41:00.34 Logon Error: 18456, Severity: 14, State: 11.
2006-08-13 21:41:00.34 Logon Login failed for user 'LAPTOP\Guest'. [CLIENT: 192.168.0.5]
2006-08-13 21:45:10.64 Logon Error: 18456, Severity: 14, State: 11.
2006-08-13 21:45:10.64 Logon Login failed for user 'LAPTOP\Guest'. [CLIENT: 192.168.0.5]
2006-08-13 21:48:41.80 Logon Error: 18456, Severity: 14, State: 11.
2006-08-13 21:48:41.80 Logon Login failed for user 'LAPTOP\Guest'. [CLIENT: 192.168.0.5]

Now I know it is actually Error: 18456, Severity: 14, State: 11.

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

11 and 12

Valid login but server access failure

It tells the connection string and SQL Express seem to be set up properly but the server access failed the remote connection

I have previously had SQL Server 2000 installed. I uninstalled SQL 2000 before I install SQL express but somehow the SQL Server Service Manager is still running at startup, and C:\Program Files\Microsoft SQL Server\80 and its files are still exist after uninstallation..... Could this be a problem?

The Knowledge base suggestion on "enabling remote connection" is very simple and I do not understand why it is so difficult to me just to make a remote connection test work..... please, I need your help.

If you want to use Windows Authentication you have to disable the "Simple File and printer sharing" at the "server" (even if its only WIndows XP). With this option enabled, it normally simplifies the logon process as the requestor does not have to give a user name and a password, because he will be logged on with the Guest user.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||I did try to disable the file sharing, and also have firewall disabled. But it still gives me the same error..... |||

OK, did you disable that on the "server" (Whereever SQL Server is running on) ?

-Jens.

|||

Yes, to make sure file sharing is disabled , I unlick the Simple File Sharing from Folder Option on both computers.

Both computer have file sharing disabled

filewall disabled

But I am still getting the same error.

Anything to do with MSDE 2000 instance? I have uninstalled MSDE2000 before I install SQL expresss.

What is wrong still?

|||

By the way, I restarted both computer and still got the same error on the client computer while the hosting computer is connecting to the KIT\SQLExpress alright :

C:\Documents and Settings\Kit>sqlcmd -E -S kit\sqlexpress
1>
2>
3>
4>
5>

Here is the current setting of my SQLexpress instance

SQL Server Config. Manager
Protoal: Shared Memory, TCP/IP enabled (Name Pipes and VIA disabled)
SQL server (SQLExpress) and SQL server Browser are running , startmode: automatic
For SQL Server Browser: Log on as : Bulit-in account: Network Service
For SQL Server (SQLExpress): Log on as : Bulit-in account: Network Service
Under Service - General -
Host Name: KIT
Name: SQL Server (SQLExpress)

SQL Native Config. - Client Protocols - Shard Memory 1, TCP/IP 2

SQL Server Surface Area Config.
Service Name: MSSQL$SQLEXPRESS
Display Name: SQL Server (SQLEXPRESS)
Remote COnnection: Local and Remote connection clicked - Using TCP/IP Only clicked

The client computer do not have any SQL express installed, could that be the reason?

Frankly, on the server computer , it can connect to the database on TCP/IP using SQL Management Studio

Here I attached the screen shots

http://www.megaupload.com/?d=JWN349QL

|||

I have a few questions about your setup:

- on the client machine, what account is your application running under?
- when you tried connecting to SQL Express locally, using sqlcmd, what was the account you actually connected as? (you can execute "select suser_name()" to find this out)

Thanks
Laurentiu

|||

Client is running Win XP Professional SP2

Server is running Win XP pro SP2

Client machine is logged in as Admin , without password (so it goes straight into the OS when you start up the machine and bypass the log in screen)

Server machine is also logged in as Admin, with passowrd (log-in: Kit, password: Kit) , TweakUI is setup to log in automatically using the log-in/password pair.

- when you tried connecting to SQL Express locally, using sqlcmd, what was the account you actually connected as? (you can execute "select suser_name()" to find this out) :

C:\Documents and Settings\Kit>sqlcmd -E -S kit\sqlexpress
1> select suser_name()
2> go

--
KIT\Kit

(1 rows affected)
1>

Hope this will give you idea of what has gone wrong with my SQL express setting

Thank you for your response.

|||

On the Server Computer , under Control Panel -> User Accounts , there are 3 accounts: Kit (myself), SQLDebugger and Guest

Guest account is not password protected and is set to off. (I've tried to turn this account on and off but the client computer still give me the same error)

I really havae no idea what I have gone it wrong...

|||

I just realize what you meant by "on the client machine, what account is your application running under?"

It is running under Network Service

|||

Locally, you connect as Kit, which seems to have server access. Remotely, you appear to execute as Network Service and when you get on the server machine, you are actually running as Guest, which does not have server access.

If you execute

CREATE LOGIN [Laptop\Guest] FROM WINDOWS

then you will grant server access to the Guest account.

But if you want your application to connect as Kit, you should run it as Kit or it should internally impersonate Kit before connecting to the server.

Thanks
Laurentiu

|||

To Laurentiu and Jens:

Thank you for your great help and now the client can connect to the server SQL express. I have been myself studying the SQL server and exploring how SQL server work. This SQL express is very interesting and I have a lot more to learn about this "toy".

I am very appreciated to your helps.

Kit