|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Persist Security Info = True in connection stringPer 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. 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 -- Show quoteHide quote____________________________________ 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" <g***@cds-am.net> wrote in message news:%23aXseDkgFHA.2180@TK2MSFTNGP15.phx.gbl... > 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. > > > > 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? *** Sent via Developersdex http://www.developersdex.com *** 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. Show quoteHide quote "Greg Robinson" <g***@cds-am.net> wrote in message news:%23RevEBlgFHA.2080@TK2MSFTNGP10.phx.gbl... > 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? > > > *** Sent via Developersdex http://www.developersdex.com *** 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. -- Show quoteHide quote____________________________________ 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" <g***@cds-am.net> wrote in message news:uPAxQ5lgFHA.3656@TK2MSFTNGP09.phx.gbl... >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. > > > > > "Greg Robinson" <g***@cds-am.net> wrote in message > news:%23RevEBlgFHA.2080@TK2MSFTNGP10.phx.gbl... >> 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? >> >> >> *** Sent via Developersdex http://www.developersdex.com *** > >  
Other interesting topics
Q:Multithreaded ADO.Net Connections Are Non Pooled?
Update ado.net DataSet referential integrity NTAUTHORITY\NET SERVICE not authorized really daring subject! DataGrid MyBase.OnPaint -> content Question regarding OOP and database access rowfilter not working correctly General Network Error - LocalHost SQL statement with multiple WHERE statements Difference between a datatable and dataview |
|||||||||||||||||||||||
