Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
swhetsell
Starting Member
4 Posts |
 Posted - 2010-05-13 : 19:30:04
|
| I am using an Access 2k3 front end interface to connect to SQL Server 2k8 over our network. The access front end utilizes a DSN connection to talk with SQL. The machine hosting SQL has been running heavy lately and reporting unusually heavy batch loads. I loaded the current event log to search for answers and discovered that in the last 3 weeks over 500,000 log entries have been created for successful logins. It is reporting a successful login on average every 5 to 10 seconds. I understand DSN isn't the preferred connection method, but I've only been using SQL for a few months so I'm still getting my footings. All computers are using the same login information for the DSN. Any ideas on what could be causing the excessive activity or how to fix it?PCO S P Whetsell, A.A.S. |
|
|
vmenon
Starting Member
17 Posts |
Posted - 2010-05-14 : 01:47:17
|
| You say "All computers are using the same login information for the DSN". I take this to understand that you mean, you are using SQL authentication by having the SQL login id and password configured as part of the DSN configuration / creation.I recommend that you don't use SQL authentication. Ideally while creating a DSN you will be presented with an option of "Windows authentication using network ID". You should be using this.The advantage, when success login(s) are logged, the network login ID information will also be logged and this information can be used to determine which user / from system is generating too many login attempts.In your case since everyone is / will be using the same SQL login ID, the log entries information will contain the same ID. Not a good strategy, as this log information does not allow us to filter down to problem source to understand who / which system is causing the excessive login(S).On your statement "I understand DSN isn't the preferred connection method", my understanding is system DSN is one of the preferred way of connecting from Microsoft ACCESS to external data sources like "SQL Server", "Oracel" or any third party databases. |
 |
|
|
swhetsell
Starting Member
4 Posts |
Posted - 2010-05-14 : 17:23:18
|
| Sorry for not clarifying.Yes, we are using SQL Authentication with the username and password saved in the DSN configuration. The DSN is configured as a System DSN on each of the machines. As far as not preferred, it is my understanding that best practice is to use DSN-less connections opposed to System or User DSNs. If I'm wrong about that, I'll stand corrected.I would like to use Windows authentication, but the way our network is configured we do not have individual logins to Windows (running XP SP3). The systems are stand alone and only the business applications make network connections to the appropriate servers, not windows itself.The log is identifying the computers by IP, so i can track most of the entries back to two or three computers. However, they are all imaged machines so I don't know what would cause the few to create so many connection attempts.PCO S P Whetsell, A.A.S. |
|
|
|
|
|
|
|
|
Connection Issues