sql server - Why does creating contained authentication user cause login error? -


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