Need help with connect string for using Application Roles in SQL Server.

[Ken Varn]

I am trying to figure out how to do a connection string for a ADO.NET
SqlConnection object on a database that implements Application Roles. I
cannot seem to get the connection to work. I am of the impression that an
initial connection must be made to the database and then the sp_setapprole
stored procedure must be run to get the proper Application Role permission
to the database. I cannot get past the Open() method of the connection
object to get to a position where I can run the sp_setapprole stored
procedure. Is there some specific connection string that I need to use?
Please help!


--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------

 

[Jeff Dillon]

No, without the proper login, you won't be able to connect to the database
at all.

Have you succeeded in making a connection without using Application Roles?

Also, show your connection string

Jeff

 

[Eric Newton]

unfortunately Sql Application Roles is SQL only, last time I checked into
this, I was told that ADO and ADO.Net didnt
truly support the Application Roles feature.

...Even though the application roles make a LOT more sense than arbitrary
logins, whereas certain logins can have
a lot of access, but only when using "Sql Query Analyzer" to some degree.

However, if I'm not mistaken, ADO.Net doesnt support the application roles.

 

[Eladio Rincón]

Hi,
ADO.NET supports application roles; what it is not supported are application
roles with connection pooling.
if you open a connection "pooled", you set the application role, and you run
a query, when you run the query you receive a "General Network Error".

Regards,
Eladio

 

[Ken Varn]

If it does support application roles, then could you give a code snippet on
how to do the connection?

I setup the BUILTIN\Guests group in my Logins list. I have tried several
different connection strings and have not had any luck. Any suggestions?

--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------

 

[Ken Varn]

I have tried several different connection strings:

MyConnect.ConnectionString = "Server=(local);Integrated
Security=SSPI;Database=MyDB";

or

MyConnect.ConnectionString = "Server=(local);Integrated
Security=false;Database=MyDB;User ID=guest";

or

MyConnect.ConnectionString = "Server=(local);Integrated
Security=true;Database=MyDB;User ID=guest";



None have worked when I try to open the database. The SQL Server has
BUILTIN\Guests in the list of Logins under the Security item. Not sure why
it won't let me connect.

--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------

No, without the proper login, you won't be able to connect to the database
at all.

Have you succeeded in making a connection without using Application Roles?

Also, show your connection string

Jeff

 

[Eladio Rincón]

Sure !
this is the example; if you modify the example for Pooling=true, the first
attempt will run because you have no connections in the pool, the second one
will fail;

with this code you need to create an application role named myRole whose
password is myPassword

Try
Dim c As New
SqlConnection("server=.;database=Northwind;Trusted_Connection=SSPI;Pooling=f
alse;")
Dim cmd As New SqlCommand("sp_setapprole")
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = c
cmd.Parameters.Add("@rolename", "myRole")
cmd.Parameters.Add("@Password", "myPassword")
c.Open()
cmd.ExecuteNonQuery()
cmd.CommandType = CommandType.Text
cmd.CommandText = "select count(*) from dbo.Orders"
Dim dr As SqlDataReader = cmd.ExecuteReader
dr.Read()
Console.WriteLine(dr(0))
c.Close()
Catch ex As Exception
Console.WriteLine(ex.ToString)
End Try

--
Eladio

If it does support application roles, then could you give a code snippet on
how to do the connection?

I setup the BUILTIN\Guests group in my Logins list. I have tried several
different connection strings and have not had any luck. Any suggestions?

--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------

 

[Ken Varn]

EXCELLENT!

That seems to work great. Thanks a lot!

I found one other thing that I had to do as well. Since I was calling this
from ASP.NET, I had to add my ASPNET user to the guests group. Once I did
that, it worked.

--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------