Solved

Failed login to my local sql server 2005

Posted on 2009-05-08
19
763 Views
Last Modified: 2012-05-06
I have an web application which is was developed in ASP.NET v1.1 and database is sql server 2000. In the beginning of this month, my boss decide to update the database system from sql server 2000 to sql server 2005. Well, I made it. I setup a server with MS Windows 2000 Server, SQL Server 2005 Developer Edition, and .NET Framework, of course. After that, I restore my 10GB database into sql server 2005, edit the web.config as:
<add key="ConnectionString" value="user id=sa;data source=CTNKHSER;initial catalog=BILLING;password=sqlpassword"/>

Well, my web app runs smoothly with 40 users in my company everyday. But when I check the LOG view in the sql server 2005, I realised that it's fulled with the Error 18456 Severity, state 8 (see the attached picture). And I don't know why.  

I know the password is correct as it is coming from the config file and not changing. I tried deleting the users and recreating them by hand but still have the same sporadic problem. I know all the state 8 of the 18456 Severity, and case sensitive of sql 2005 password. In fact, if my password is wrong, my web app can not run. It still running, but log error still alert.

As the result, my server automatically restart after 3-4 hours running.
Web and Database are in the same server.
Anybody can help? my business is go down and make me so confuse.
Here is one of error log from my sql server 2005:
 

Date		5/9/2009 11:16:24 AM

Log		SQL Server (Current - 5/9/2009 11:16:00 AM)

Source		Logon
 

Message

Login failed for user 'sa'. [CLIENT: 192.168.58.4]
 

Please Note that the IP address  192.168.58.4 is my server IP

Open in new window

Log.jpg
log-connect.txt
0
Comment
Question by:binhtd0111g
  • 12
  • 7
19 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24342334
are you able to get login by SA in SSMS?
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24342358
Moreover, I am seeing that SA login failed from few different IP and you told me that you are getting connection from your web application only, so every IP belongs to your network only, Actually IP belong to 192.168 series only so it might be from your network only but I have doubts that somebody from outside is trying to connect to your 1433 port via your computer, since they don't know password for SA so login gets fails. I highly recommend you to create one user for your web application and set it in web.config and have strong password for SA, don't use it in your application.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24342360
I recomend you to read following post, Me and AngelIII made comment there.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24393956.html
0
 

Author Comment

by:binhtd0111g
ID: 24342381
RiteshShah:
I still login to SSMS by user SA.
Let me try to user another new user for my web app, I will report you soon.

Thanks.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24342389
login from other user in web app will not solve problem, that is just for security purpose. Actually I have strong doubts that outsiders are trying to get login to your SQL Server, I would recommend you to change SQL Server port from 1433 to something else, may be 1533 and use it in your application. Moreover, if possible, close the SQL server port for public and open it for your web applicaton only and for few trusted IP.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24342403
Just to prove whether I am right or wrong, you can try one thing, get your server out of network. you have both web and database on the same server so try dis connect your server from network, try to access your web application and database directly from SSMS for few hours and see whether you get any log in event viewer, I am 100% sure, you won't get any. If you won't get any log than it itself proves that you have risk from outsiders, than you will have to think how you can protect your server.
0
 

Author Comment

by:binhtd0111g
ID: 24342430
Login failed for user 'sa'. [CLIENT: 192.168.58.4]

Actually, in the log windows, most of login failed come from the server itself. The IP 192.168.58.4 is the IP of Web + Database server. It try to connect itself and getting fail.

Now I disable SA Account, create another account for web app, and the log file now is
Error 18470 Severity 14, State 1 (yeah, of course it's the bug come from disable account)

I'm going to disconnect my server from the network and do the observation on it now...
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24342442
don't forget to disconnect from internet also, if you have direct connection of internet over it.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24342872
any update so far?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:binhtd0111g
ID: 24342897
Well, I disconnect the Server (web + db) 1 hour ago, and nothing increase in the Log windows. I'm continue to observe the server... (see the newest attach file)
Srv-Log-2009.jpg
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24342903
so now do you think my doubts were right? someone trying to connect to your 1433 port of SQL server. I have already described the steps to prevent that.

BTW, you have disconnected the server but have you used it locally, like open website, get connection to SSMS and execute some queries etc.?????
0
 

Author Comment

by:binhtd0111g
ID: 24342910
RiteshShah:
BTW, you have disconnected the server but have you used it locally, like open website, get connection to SSMS and execute some queries etc.?????
====================================

Yes, I tried to get connect to SSMS and manipulate (DML, DDL) with my Sql Server 2005 server and everything still work well.

====================================
I would recommend you to change SQL Server port from 1433 to something else, may be 1533 and use it in your application. Moreover, if possible, close the SQL server port for public and open it for your web applicaton only and for few trusted IP.
====================================

Well, now I'm gonna do your suggestion... I believe your idea is 99% right ;)
0
 

Author Comment

by:binhtd0111g
ID: 24342930
I've just change the default port of the sql server 2005 from 1433 to 1533:
(so now I'm continue observer the server log)
1/ Click Start, point to All Programs, click Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

2/ Expand SQL Native Client Configuration, click Client Protocols, and then double-click TCP/IP.

3/ Specify the port number that you want in the Default port box, and then click OK.

4/ Restart IIS. To do this, click Start, click Run, type iisreset in the Open box, and then click OK.

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24342945
Is your website working? I guess you have to give port 1533 in your connection string.
0
 

Author Comment

by:binhtd0111g
ID: 24342959
my connection string is connect through name pine:

<add key="ConnectionString" value="user id=sa;data source=CTNKHSER;initial catalog=BILLING;password=sqlpassword"/>

so the website is still working well, do I need to modify the connection string by IP?
such as...
Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24342962
if it is working and connected with namepipe than don't need to change anything.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24343269
actually I was looking for A grade but got B, anyway, its ok.
0
 

Author Comment

by:binhtd0111g
ID: 24343465
Well, I don't know that, I tried to accept the solution, It's the first time I try to use this website... so what's the different? It will add more point to your profile?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24343478
well, it grade A and grade B make big difference, if you really like my solutions than you can increase the rank, don't need to give me more points than you have decided while asking the question, if you don't know how to do it, you can use "Request Attention" link on the question section of this page.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to enforce inte 8 32
Testing connection to sql 7 52
Divide by zero error encountered. 2 30
CREATE DATABASE ENCRYPTION KEY 1 45
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now