February 22, 2008 at 10:26 am
Hi Matt
I guess you're referring to the original connection error for "login failed"? The "state" is always a '1' with a "severity" 14:-
Server Name: xx.xxx.xxx.x
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
I can post the whole error log for this error instance if it would help decipher things?
February 22, 2008 at 10:43 am
You need to go dig up the ACTUAL error from the SQL Server error logs. It would look something like:
2008-02-22 12:37:41.46 Logon Error: 18456, Severity: 14, State: 8.
2008-02-22 12:37:41.46 Logon Login failed for user MyUserName. [CLIENT: a.b.c.d]
Your logs are in test format in a LOG folder in the SQL Server directory structure. Using the default directory structure - mine are in :
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
Like he mentioned at the beginning of the blog entry - 1 is returned to "protect the server" (as to not steer a hacker into what direction the failure is pointing). The server log on the other hand should tell you something.
Here's hoping he's actually still stating fact when he said "that logging is turned on by default".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2008 at 10:58 am
Ok interesting - here's the last two log entries. I tried connecting first with a user I created (I changed the name here for security reasons!). Secondly I tried with SA - I tried locally with both users and they can connect with the passwords all being right.
2008-02-22 17:55:51.89 Logon Error: 18456, Severity: 14, State: 5.
2008-02-22 17:55:51.89 Logon Login failed for user 'testuser1'. [CLIENT: xxx.xx.xxx.xx]
2008-02-22 17:56:50.46 Logon Error: 18456, Severity: 14, State: 8.
2008-02-22 17:56:50.46 Logon Login failed for user 'sa'. [CLIENT: xxx.xx.xxx.xx]
February 22, 2008 at 11:14 am
Just curious - when you say "from outside" what do you mean? Surely you don't mean - from your machine over the internet?
That 5 and 8 mean invalid user and invalid password, respectively. That would also possibly signify local admin ports being blocked (which you would expect if it's an internet server).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2008 at 11:42 am
Yes, exactly - from my machine to a dedicated web server that has MS SQL Server 2005 installed.
When you say "local admin ports being blocked"... I'm presuming the hosting company aren't doing that? Where should I check to unblock anything - I've checked/enabled things that would seem detrimental to what I'm trying to do.
Surely it can't be this hard to connect to a server's SQL instance?
February 22, 2008 at 12:24 pm
It's very likely being blocked by the hosting company. Opening the ports you need would be a hacker's heaven - meaning - a wide open avenue right into your client's data for anyone to hammer on until they get in. they block them because they would be the same ports required to hack in and take over the server itself.
The fact that it's even visible over the wire is scary enough. That's not something you want. Really - you don't. You should look at reversing your previous config and putting it so that it only takes local connections, and configure your web app to connect locally. Otherwise - that's just a timebomb ticking away.
Sorry - it took me all of this time to figure out what you're trying to do. You don't want a SQL server in the open like this.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2008 at 1:37 pm
Hi Matt,
Thank you so much for your advice... whilst it wasn't what I really wanted to hear, it woke me up and made perfect sense.
Kinda strange why the hosting company couldn't tell me this isn't it? Now I suppose I'll be forced to have a separate SQL Server machine, which my client will probably have to pay for.
Again, many thanks Matt.
Andy
February 22, 2008 at 1:41 pm
The one thing you might ask them is if they can prevent access to SQL from anything other than that box (that's a firewall setting). Meaning - the only thing that can "talk" to the SQL Server is something running on the actual local box, be it SSMS, or IIS. Anything originating off of the virtual server is denied access to SQL server. (that's essentially how they have their SQL servers set up.)
That's a second way to configure it. But bottom line though - you would need to build the app locally, then publish it to that server, and make sure that nothing other than the web portion is available from the web.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply