Solved

Failed login to my local sql server 2005

Posted on 2009-05-08
19
764 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Backup & Restore 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.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 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

20 Experts available now in Live!

Get 1:1 Help Now