Persist Security Info = True in connection string

[Greg Robinson]

Sql Server connection string, Persist Security Info = True\False

Per the docs I find:

"When set to false or no (strongly recommended), security-sensitive
information, such as the password, is not returned as part of the
connection if the connection is open or has ever been in an open
state. Resetting the connection string resets all connection string
values including the password. Recognized values are true, false, yes,
and no."

I have always used False.

This has worked for all of our clients using Sql Server 7.0.

We did 2 installs this week where the clients are using Sql Server 2000.

Calls to select sprocs worked, all calls to insert, update or delete sprocs
failed with a Log In failed for user exception.

We pass the id and password with our connection string.

Our DAL code opens the connection, calls BeginTransaction and then calls the
update sproc. The call to the update sproc, when Persist Security
Info=False fails (Login In fails), though when it's true, it succeeds. So
it sounds like, even the the connection is already open, something about
executing the sproc tries to open the connection again, there is no
password, so it fails? This makes no sense to me as the id and password are
included in our connection, the connection that is already open.

Anybody know what all this means?

If I open a connection, call a SELECT sproc, it works. If I open the same
exact connection (it may still even be in the pool as it is within 1-2
seconds), call an UPDATE sproc with Persist Security Info set to False in
that connection string, I get a long in failed for user exception fro sql
server. If I change this value to True in the connection string, all sprocs
work. So far I have seen a seting of False work for updates on a 7.0 Sql
Server and not work on a 2000 Sql Server.

 

[William \(Bill\) Vaughn]

The Persist Security Info simply tells ADO.NET not to expose the credentials
you use to code that references the specific credentials. The
ConnectionString that's used to build the connection pool and connect to
your server must use (and contains) these credentials. However, if you fetch
the ConnectionString property of an open Connection, the credentials are
stripped out before returning them to you. Since you're passing the
Connection string to another layer, I suspect that at that point you don't
have a connection just a ConnectionString that's been stripped of its
credentials. SP's can't open connections, but they might have specific
rights that might be different than those assigned to a database table.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Greg Robinson]

Bill, thanks for the reply.

Our client is a .NET windows form application.

Our Data Access Layer is hosted by IIS as a remote server.

IIS and Sql Server 2000, at these 2 client sites, are on the same box.

The windows client reads the encrypted connection string from the
app.config file. Persist Security Info was initally set to False. This
string is passed to the DAL, which decrypts it and opens a connection
and begins a transaction:

Try
conn.Open()
txn = conn.BeginTransaction()


Prior to this "transaction" Try block, we do a select, which works fine:

Try
da.Fill(ds)
Finally
cmd.Parameters.Clear()
End Try

I know the call to Fill on the select opens and closes the connection so
I do not call Close or Dispose in the Finally. So, the select works,
the update right after the select does not.

The update 'manually' opens the connection:
Try
conn.Open()
so I can start the transaction.

We call DataAdapter.Update to actually call into sql server to do the
update. My understand with this method (Update) is the same as Fill, if
the connection is not open it will open it. The connection should still
be open IMHO.

So the question is why does this 'work' for all selects though fail for
an update, and, what's actually failing? I know the conn.Open code
executes successfully, so what is trying to re-open the connection that
can't?

Why does setting Persist Security Info=True make (so what is trying to
re-open the connection that can't?) happy?

 

[Greg Robinson]

I see a Connection Reset parameter which is true by default. Per the docs:

"Determines whether the database connection is reset when being drawn from
the pool. For Microsoft SQL Server version 7.0, setting to false avoids
making an additional server round trip when obtaining a connection, but you
must be aware that the connection state, such as database context, is not
being reset."

Why is the connection reset? I thought the whole idea behind connection
pooling was to avoid the overhead associated with getting a connection. I
guess I am grabbing at straws here to figure out the difference in a
connection for a select and a connect for an update and also the difference
in sql server 7 and sql server 2000.

 

[William \(Bill\) Vaughn]

Without the reset, each connection would inherit the dirty sheets and dishes
in the sink from the previous tenant. Sure, sometimes you want this
behavior, but it's dangerous to assume so.

I think you need to read my whitepaper about the connection pool. See
www.betav.com\articles.htm.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________