Failed login to my local sql server 2005

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
binhtd0111gAsked:
Who is Participating?
 
RiteshShahConnect With a Mentor Commented:
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
 
RiteshShahCommented:
are you able to get login by SA in SSMS?
0
 
RiteshShahCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
binhtd0111gAuthor Commented:
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
 
RiteshShahCommented:
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
 
RiteshShahCommented:
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
 
binhtd0111gAuthor Commented:
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
 
RiteshShahCommented:
don't forget to disconnect from internet also, if you have direct connection of internet over it.
0
 
RiteshShahCommented:
any update so far?
0
 
binhtd0111gAuthor Commented:
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
 
RiteshShahCommented:
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
 
binhtd0111gAuthor Commented:
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
 
binhtd0111gAuthor Commented:
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
 
RiteshShahCommented:
Is your website working? I guess you have to give port 1533 in your connection string.
0
 
binhtd0111gAuthor Commented:
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
 
RiteshShahCommented:
if it is working and connected with namepipe than don't need to change anything.
0
 
RiteshShahCommented:
actually I was looking for A grade but got B, anyway, its ok.
0
 
binhtd0111gAuthor Commented:
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
 
RiteshShahCommented:
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
All Courses

From novice to tech pro — start learning today.