How to connect, after all ?

Hi All,

i have a problem, so lets to it.

Situation:

I have a COM that connects to SQL2K with that string connection:
"Provider=SQLOLEDB;Persist Security Info = False;DATABASE=MyDatabase;SERVER=MyServer;User Id=myuser;Password=123456;"

that works fine, but we have a problem: the person that compiles de DLL knows user and password, so we decided to change the way the application connects to SQL, and start using AppRoles.

Things we did:
1-I created an approle and gave the permissions to the role.

2-I grant connect permission to domain\user1, only connect, that will be used when registering the COM as domain\user responsible to be used to stablish the connection.

3-Developers changed string connection to: "Provider='SQLOLEDB';Data Source='MyServer';Initial Catalog='Mydatabase';Integrated Security='SSPI';"

New behavior:

The connection goes OK, but the way we did before, when traced with profiler, the sp_reset_connection has been invoked by sqlserver, and with the knew connect string was no more executed.

Another problem is that when the COM is called in a second time, the previous connection is logged out and it stops, not connecting again.

Any ideas ??

thanks a lot.

Leandro.What's the source code of sp_reset_connection ( are you sure this is a standard MS sp)|||i dont know, sp_reset_connection is invoked by sqlserver that controls pooling connection.

the fact is that with the string connection used before the sp_reset... has been invoked, and with the new configuration was no more.

we setup a domain\user responsible to the COM and granted connect permission to it and the code of the COM is executing sp_setapprole.

the first time the COM is called it works fine, but the second time the sp_reset_connection is not invoked, and yhe connection is logged out.

the third time the COM is invoked all works fine again, when i trace the connection the select runs OK, ando so on.

any ideas ?|||I would try adding "Persist Security Info = False;" back into your connection string