I am having a very strange problem and am hoping someone out there has had a similar experience.
My companies application for one client is getting "banned" from the SQL Server at the beginning of our application. The behavior is strange. I'll write it out in point form.
SQL Connections are created, data is retrieved, the connections are closed, talk to another datasource and then denied access to SQL Server.
Here's the long winded version:
.NET application connects to database multiple times. Gets some data, does some work. It then goes to get some more data and then gets an error that the "SQL Server cannot be found or access is denied". If the process is started over again without re-starting the app then no more connections are able to be made to SQL Server. All new connections result in "SQL Server cannot be found or access is denied". If the application is restarted then it will repeat the above process.
This is the first in 5 years of my experience with the software to have this problem. The application does have code written in Delphi 7. The dephi 7 / VBA code has not issues. My .NET code that performs the actual query looks like:
protected abstract DbConnection GetConnection();
protected abstract DbDataAdapter GetDataAdapter(DbCommand cmd);
protected abstract DbCommand GetCommand(DbConnection conn, String sql);
protected abstract DbCommandBuilder GetCommandBuilder(DbDataAdapter adapter);
public virtual DataTable Query(string sql)
{
var dt = new DataTable();
using (var conn = GetConnection())
{
try
{
using (var cmd = GetCommand(conn, sql))
{
using (var adapter = GetDataAdapter(cmd))
{
adapter.Fill(dt);
}
}
}
catch (Exception ex)
{
throw new SqlStatementException(sql, ex);
}
}
return dt;
}
It is my own quite and dirty DAL. When it is used it is using an OleDbConnection.
Note: Due to legacy code the connection string is configured for OleDbConnection. After taking a moment to review my code I do have the ability to change the connection type to SqlConnection. I haven't tried that yet.
On the client's machine I have not been able to reproduce the issue outside of the main application. I tried creating a little app that would make 100 calls back to back using the format above with an OleDbConnection but it executed successfully.
The failure in the main app happens in the same spot. That should give me a clue except I cannot make sense of it since it is making duplicate query, getting the same data. But I will say that the application talks to two data sources and transfers data from one to the other. Before it does the transfer it does some validation on the sources. So it talks to another database (proprietary file based) via ODBC and comes back successfully and then fails when trying to talk to SQL Server through OleDbConnection.
My suspicion is something is happening in the connection pool. That is causing a failure which in turns causes a denial of access.
Other interesting points. All worked fine for about a year, client got a new machine a couple of months ago, all work fine and then suddenly stopped. I put the application on another machine at the client's site and all worked well for a week and then the same issue appeared. We turned everything off on the client's machine but the issue persisted. I thought firewall but no luck there.
Any assistance is greatly appreciated.
