|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server Application RolesIs this the correct way to use application roles?
Public Function GetDBConnection() As SqlConnection Dim oCon As New SqlConnection(myConnectionString) oCon.Open() Using oCmd As SqlCommand = oCon.CreateCommand oCmd.CommandText = "IF NOT User_NAME() = 'app_name' Exec sp_setapprole 'app_name' ,'password" oCmd.ExecuteNonQuery() End Using Return oCon What is a good way to embed/encrypt the application role's password in the program? -- Jonathan Allen Allow me to restate my question.
What the hell is the right way to use application roles from VB/C#? When I try the below code, I get an exception the second time I call GetDBConnection. -- Show quoteJonathan Allen "Jonathan Allen" <x@x.x> wrote in message news:eF4AhZV7FHA.472@TK2MSFTNGP15.phx.gbl... > Is this the correct way to use application roles? > > Public Function GetDBConnection() As SqlConnection > Dim oCon As New SqlConnection(myConnectionString) > oCon.Open() > Using oCmd As SqlCommand = oCon.CreateCommand > oCmd.CommandText = "IF NOT User_NAME() = 'app_name' Exec > sp_setapprole 'app_name' ,'password" > oCmd.ExecuteNonQuery() > End Using > Return oCon > > What is a good way to embed/encrypt the application role's password in the > program? > > -- > Jonathan Allen > > > > Applications roles work the same way normal users works... IMHO you
shouldnt be trying to create them from you application... you should define them when configuring your database for deployment (or development) ... the main (and only?) difference between app roles and users... is that app roles are "saved" inside your database (while users are saved in the database server) so if you backup and restore you database in multiple sqlserver you app roles will continue to work... while you user might have to be reatached (because their internal id keys could be different in different dabase servers)... I hope that helps ;) . Jonathan Allen wrote: Show quote > Allow me to restate my question. > > What the hell is the right way to use application roles from VB/C#? When I > try the below code, I get an exception the second time I call > GetDBConnection. > > Applications roles work the same way normal users works.... Application roles might appear similar to normal users in some areas but these are actually very different. A login is first authenticated at the SQL Server level. If valid, SQL Server checks to see if the login has access to the requested database by looking for a database user mapped to that login. If present, access to the database is allowed to the database under the security context of the database user. An database application role be activated only after SQL Server allows database access. As you indicated, a login/user mismatch can occur when a database is attached or restored to another server. This problem can be addressed by either recreating the problem database users or executing sp_change_users_login to correct the problem. It is naive to assume that application roles will circumvent the mismatched user problem because not only is database access not guaranteed, the database could be accessed under the wrong security context before the app role is activated. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "luxspes" <m*@privacy.net> wrote in message news:uWjEoRb7FHA.2384@TK2MSFTNGP12.phx.gbl... > Applications roles work the same way normal users works... IMHO you > shouldnt be trying to create them from you application... you should > define them when configuring your database for deployment (or development) > ... the main (and only?) difference between app roles and users... is that > app roles are "saved" inside your database (while users are saved in the > database server) so if you backup and restore you database in multiple > sqlserver you app roles will continue to work... while you user might have > to be reatached (because their internal id keys could be different in > different dabase servers)... > I hope that helps ;) . > > > Jonathan Allen wrote: >> Allow me to restate my question. >> >> What the hell is the right way to use application roles from VB/C#? When >> I try the below code, I get an exception the second time I call >> GetDBConnection. >> I assume you are using SQL 2000. Application roles have been deprecated in
SQL Server 2005. You need a single-quote after the password literal. Also, when you use application roles, consider disabling connection pooling. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Jonathan Allen" <x@x.x> wrote in message news:eeb26qV7FHA.3388@TK2MSFTNGP11.phx.gbl... > Allow me to restate my question. > > What the hell is the right way to use application roles from VB/C#? When I > try the below code, I get an exception the second time I call > GetDBConnection. > > -- > Jonathan Allen > > > "Jonathan Allen" <x@x.x> wrote in message > news:eF4AhZV7FHA.472@TK2MSFTNGP15.phx.gbl... >> Is this the correct way to use application roles? >> >> Public Function GetDBConnection() As SqlConnection >> Dim oCon As New SqlConnection(myConnectionString) >> oCon.Open() >> Using oCmd As SqlCommand = oCon.CreateCommand >> oCmd.CommandText = "IF NOT User_NAME() = 'app_name' Exec >> sp_setapprole 'app_name' ,'password" >> oCmd.ExecuteNonQuery() >> End Using >> Return oCon >> >> What is a good way to embed/encrypt the application role's password in >> the program? >> >> -- >> Jonathan Allen >> >> >> >> > > Au contraire.
Application roles have certainly not been deprecated in SQL 2005. They are very much alive and well and actually improved. In SQL 2005 you can actually unset the application role and return back to the user's original context. This is conveninent in allowing you to move in and out of different parts of an application with different security contexts. One application role might have broad rights in one part of a schema while another might have broad rights in another part. You might have a master control table specifying which users can walk into which application roles. Chuck Hawkins Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:uwPjvwc7FHA.3636@TK2MSFTNGP09.phx.gbl... >I assume you are using SQL 2000. Application roles have been deprecated in >SQL Server 2005. > > You need a single-quote after the password literal. Also, when you use > application roles, consider disabling connection pooling. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Jonathan Allen" <x@x.x> wrote in message > news:eeb26qV7FHA.3388@TK2MSFTNGP11.phx.gbl... >> Allow me to restate my question. >> >> What the hell is the right way to use application roles from VB/C#? When >> I try the below code, I get an exception the second time I call >> GetDBConnection. >> >> -- >> Jonathan Allen >> >> >> "Jonathan Allen" <x@x.x> wrote in message >> news:eF4AhZV7FHA.472@TK2MSFTNGP15.phx.gbl... >>> Is this the correct way to use application roles? >>> >>> Public Function GetDBConnection() As SqlConnection >>> Dim oCon As New SqlConnection(myConnectionString) >>> oCon.Open() >>> Using oCmd As SqlCommand = oCon.CreateCommand >>> oCmd.CommandText = "IF NOT User_NAME() = 'app_name' Exec >>> sp_setapprole 'app_name' ,'password" >>> oCmd.ExecuteNonQuery() >>> End Using >>> Return oCon >>> >>> What is a good way to embed/encrypt the application role's password in >>> the program? >>> >>> -- >>> Jonathan Allen >>> >>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics