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

how to connect to SQL server express 2005 database at the same time from both SSMSE and VB2005 a

Here is the situation:

I have SQL server express 2005 installed on my pc as instance SQLEXPRESS.

I have created a Visual Basic applicaion with the following as connection to the SQL server express 2005 running on the same PC:
****************************************************************************************************
Dim lconnectionString As String
Dim builder As New SqlConnectionStringBuilder
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
Dim parameter As SqlParameter


builder("Data Source") = ".\SQLEXPRESS"
builder("Initial Catalog") = ""
builder("AttachDbFilename") = "C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf"
builder("Integrated Security") = True
builder("User Instance") = True
lconnectionString = builder.ConnectionString

Dim sqlConnection1 As New SqlConnection(lconnectionString)

cmd.CommandText = "SP_add_collection"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()
*******************************************************************************************************************

It seems that i can not connect to the abc.mdf in SSMSE while the VB program is running. (ERROR:
Database 'C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (.Net SqlClient Data Provider) )

If i connect to the abc.mdf first in SSMSE, then run the VB program afterwards, it gives me the error on this line -- sqlConnection1.Open()

I want to be able to access the abc.mdf database with both SSMSE and VB at the same time. Could anyone help me on this ?

Thanks very much !

apple

I'll go out on a limb here and assume that you've attached the .mdf to SSMSE which is essentially just a pointer to the db. It is not technically a SQL database.

When you're connecting the .mdf through code, use the same connection as if you were connecting to MS Access.

If you have a stored procedure that uses the .mdf, use a sql connection.

Good luck,

Adamus

|||yes, i attached the .mdf file to the SSMSE. and i call the stored procedures in the VB application.

I want to see the result of data change of the VB application in the SSMSE right away, while the application updates the date in the table. is it possible ?|||

Yes it's possible but you have to connect directly to the .mdf not SSMS.

Adamus

sql

how to connect to SQL server express 2005 database at the same time from both SSMSE and VB20

Here is the situation:

I have SQL server express 2005 installed on my pc as instance SQLEXPRESS.

I have created a Visual Basic applicaion with the following as connection to the SQL server express 2005 running on the same PC:
****************************************************************************************************
Dim lconnectionString As String
Dim builder As New SqlConnectionStringBuilder
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
Dim parameter As SqlParameter


builder("Data Source") = ".\SQLEXPRESS"
builder("Initial Catalog") = ""
builder("AttachDbFilename") = "C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf"
builder("Integrated Security") = True
builder("User Instance") = True
lconnectionString = builder.ConnectionString

Dim sqlConnection1 As New SqlConnection(lconnectionString)

cmd.CommandText = "SP_add_collection"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()
*******************************************************************************************************************

It seems that i can not connect to the abc.mdf in SSMSE while the VB program is running. (ERROR:
Database 'C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (.Net SqlClient Data Provider) )

If i connect to the abc.mdf first in SSMSE, then run the VB program afterwards, it gives me the error on this line -- sqlConnection1.Open()

I want to be able to access the abc.mdf database with both SSMSE and VB at the same time. Could anyone help me on this ?

Thanks very much !

apple

I'll go out on a limb here and assume that you've attached the .mdf to SSMSE which is essentially just a pointer to the db. It is not technically a SQL database.

When you're connecting the .mdf through code, use the same connection as if you were connecting to MS Access.

If you have a stored procedure that uses the .mdf, use a sql connection.

Good luck,

Adamus

|||yes, i attached the .mdf file to the SSMSE. and i call the stored procedures in the VB application.

I want to see the result of data change of the VB application in the SSMSE right away, while the application updates the date in the table. is it possible ?|||

Yes it's possible but you have to connect directly to the .mdf not SSMS.

Adamus