Understanding ASP.NET ConnectionPool and string security

I'm writing an application in ASP.NET, where I do frequent SQL Connections and by frequent I mean every 2 seconds. It's real time data application. BD Engine is SQL SERVER 2008R2.

Each user connects to at least two different databases. My problem is I still cant understand the connection pooling and how much of them connections I'll have after some queries.

I implemented the following methods:

private static string composeConnectionString(string connectTo)
{
    StringBuilder sqlSB = new StringBuilder("Data Source=");
        sqlSB.Append(dataSource);
        sqlSB.Append(";Min Pool Size=");
        sqlSB.Append(minPoolSize);
        sqlSB.Append(";Max Pool Size=");
        sqlSB.Append(maxPoolSize);
        sqlSB.Append(";Connection Timeout=");
        sqlSB.Append(connectionTimeout);
        sqlSB.Append(";Initial Catalog=");
        sqlSB.Append(connectTo);
        sqlSB.Append(";Integrated Security=");
        sqlSB.Append(integratesSecurity);
        sqlSB.Append(";User Id=");
        sqlSB.Append(userId);
        sqlSB.Append(";Password=");
        sqlSB.Append(password);
        sqlSB.Append(";MultipleActiveResultSets=");
        sqlSB.Append(multipleActiveResultSets);
    return sqlSB.ToString();
}

public static SqlConnection getConnection(string connectTo)
{
    SqlConnection connection = null;
    string connectionString = composeConnectionString(connectTo);

    try
    {
        connection = new SqlConnection(connectionString);
    }
    catch (Exception ex)
    {
        if (connection != null)
            connection = null;
        ExceptionLogger.LogException(ex, connectionString);
    }
    return connection;
}

At this point, I begin to question if new ConnectionPool is creater for every SQLConnection I seek? How secure is the connection string?

Ask me for updates if something seem blurry.

Thank you all.

Answers


  1. Depends of your configuration. If you configure your pool for at least one connection and a maximum of 3, when your first connection happens, if pooling is enabled, the connection will check for at least 1 and maximum of 3.

The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

You can read more here: https://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.110%29.aspx

  1. About the connection string, if you use user name and password as credentials, you have a security issue. You can use Windows Authentication to ensure your connection string does not have any sensitive data, or, if you're using IIS, you can store the connection string on it to protect your data.

Read more about connection string here: https://msdn.microsoft.com/pt-br/library/system.data.sqlclient.sqlconnection.connectionstring%28v=vs.110%29.aspx

And about protecting the connection string here: https://msdn.microsoft.com/en-us/library/89211k9b%28v=vs.110%29.aspx

Hope it helps.


Need Your Help

How to get builds badge image from Gitlab CI

build gitlab badge gitlab-ci

I would like to get the build badge image on my project from Gitlab CI. I know the url (documentation) :

Where Postgres database files are saved in ubuntu?

postgresql ubuntu

How can I find where Postgres 8.x database files are saved in Ubuntu 10.04 file system?