creating contained user in sql server express 2014 partially contained database, causes exception when performing queries on existing open connections.
here sql turn on contained db authentication , create database:
exec sp_configure 'contained database authentication', 1 reconfigure go create database testcontaineduserauth containment = partial; go use testcontaineduserauth; go create user testcreatoruser password='test1234'; go alter role db_owner add member testcreatoruser; go create table [dbo].[testtable]( [testtableid] [int] not null ) on [primary] go
here .net 4.6.1 code performing query (please create console project , paste in program.cs, , of course change server , instance name match yours):
using system.data.sqlclient; namespace consoleapplication { class program { static void main(string[] args) { while (true) { using (var connection = new sqlconnection("integrated security=false;data source=localhost\\sqlexpress;initial catalog=testcontaineduserauth;user id=testcreatoruser;password=test1234")) { connection.open(); using (var cmd = new sqlcommand(@"select * testtable", connection)) { cmd.executenonquery(); } } } } } }
now, after running c# program perform query in loop, run sql management studio (btw, aware of security risks associated contained databases , dbo users in contained database):
if exists(select * sys.database_principals name = n'mynewuser') drop user mynewuser; go create user mynewuser password=n'abcd1234'; go
after executing create user mynewuser
(which executes successfully) in sql management studio, i'm getting exception in c# program:
an unhandled exception of type 'system.data.sqlclient.sqlexception' occurred in system.data.dll
additional information: login failed user 's-1-9-3-1497860641-1239606672-4234875017-3655542527'.
a severe error occurred on current command. results, if any, should discarded.
if modify code keep reference connection , not open , close connection each query don't exception. appears though exception happening when executenonquery attempts re-login on connection after connection obtained pool, i'm not sure.
additionally if pooling turned off (pooling=false
in connection string), exception doesn't occur.
finally, after error occurs once on 1 connection, seems existing pooled connections "healed" , queries executed on existing connections successful on, until 'create user' performed.
neither keeping reference permanently open connection or turning off pooling practical solution error. appreciated.
i think right. using old connection pool.
would acceptable solution clear application pool connection?
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.clearpool.aspx
Comments
Post a Comment