Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Program Login problem

Posted on 2010-08-13
10
636 Views
Last Modified: 2012-05-10
We have two instances of SQL 2005 running.  One is 2005 Express (Timeforce) and the other is the main instance (Fusion database).  We had everything working by disabling the firewall for several months but now we are getting attacked from at external IP on the SA account of both instances so we enabled the Windows Server 2003 firewall.  The Timeforce SQL still works for my remote users but the Fusion SQL database won't let my remote users login to the SA account from the application they need to run.  I don't see where there are any specific entries in the firewall for either instance of SQL.  How can I check to see why the remote users can't login to the Fusion instance under the SA account.  The app that accesses the Fusion DB does run fine on the server itself.

Thanks SQL Instance 1 SQL Instance 2
0
Comment
Question by:chasmx1
10 Comments
 
LVL 15

Expert Comment

by:faiga16
ID: 33434502
What error are you getting when they try to access using SA? Can you post the error message? It might be only a matter of SQL Authentication mode. Check if you have both the windows account and SQL authentication enabled.
0
 

Author Comment

by:chasmx1
ID: 33434594
Attached are the error logs
SQL-Error-Message.bmp
0
 

Author Comment

by:chasmx1
ID: 33434607
error code #2
SQL-Error-Message--2.bmp
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.

 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 33435181
Well the Fusion one is using the default port of 1433 and that will be blocked by the firewall where it was not being blocked before.  The other one is using 1474 as a dynamic port and depending on whether you have SQL Browser enabled or not (I am guessing that you do since the others are able to get in to that application), the users will not see problems getting into the 1474 port.

Now, you could add a rule in the firewall for allowing 1433 port through as well as UDP 1434, but then they would be able to attack again as if the firewall was disabled.

So you either need to change the port that SQL runs on to a non-standard port than 1433 and then you could specify that port number when trying to connect.

corsair,1499
0
 

Author Comment

by:chasmx1
ID: 33435498

The hits are really a problem.  Once I disable the firewall I literally get thousands of hits within minutes on both instances of SQL.  Since the TIMEFORCE instance is running on a non-standard port why is it getting hit?  Also do you recommend moving both instances of SQL to different ports?  We also are letting 1433 traffic through our router.  Shall I shut that down?
Thanks
 
0
 
LVL 3

Accepted Solution

by:
piba earned 500 total points
ID: 33438186
if its not strictly necessary for people/services outside your network to access the SQLdatabase directly i would sure recommend blocking it in the router.

besides that i would recommend creating a SQLuser with less permissions, most applications don't really need SA access. starting with datareader datawriter access and execute permissions on all stored procedures would probably be enough, or otherwise maybe dbOwner on the database but certainly not sysadmin rights of the entire SQLserver, unless there are really good reasons for doing so.. that way if a malicious user succeeds to connect they wont be able to gain complete access over the entire server.

and like dbaduck wrote above setting a specific port for the sql instance and using "server,port" to connect might solve the actual problem
0
 

Author Comment

by:chasmx1
ID: 33439309
I can't ping the corsair server but I can Remote Desktop into it from my home via a VPN.  I can ping all the other servers at the office.  What on the Windows Firewall settings would cause this,  With the Firewall turned off pinging is no problem.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 33439345
there is protocol called ICMP that is the ping responder and it could be blocked.
0
 

Author Comment

by:chasmx1
ID: 33442138
Do you know where in the ICMP panel I can turn the pinging reply back on?
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 33442419
When you go into Windows Firewall on Windows 2003 Server you will see an Advanced tab.  On the advanced tab you will see a section on ICMP.  There will be a Settings button that will allow you to tell Windows how to respond to Ping requests.  Check the boxes you want to allow and then save it.  You should then be able to ping it.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy an entire Active Directory Domain to a dev environment 4 166
Grid querry results 41 80
Enterprise Mode 4 46
Sql Server group by 10 42
Scenerio: You have a server running Server 2003 and have applied a retail pack of Terminal Server Licenses.  You want to change servers or your server has crashed and you need to reapply the Terminal Server Licenses. When you enter the 16-digit lic…
Learn about cloud computing and its benefits for small business owners.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

789 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