using application roles

[tgru]

Hello,

I am looking to have the developers at my company start to use
application roles to gain access to SQL. The basics seem simple, add the
role to the database, use sp_setapprole to activate it.

My question is most likely really simple as well...

How do you tell the application which SQL server you want to EXEC
sp_setapprole against?

i.e. do you need to establish an OLEDB connection before calling
sp_setapprole? Seems strange to have to establish a connection twice,
but I can't think of how else to do it. But I think I'm missing
something...

Thanks in advance!

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dan Guzman]

> i.e. do you need to establish an OLEDB connection before calling

sp_setapprole? Seems strange to have to establish a connection twice,
but I can't think of how else to do it. But I think I'm missing
something...
Yes, you need to first establish a connection before your execute
sp_setapprule. This validates the user's login against the target SQL
Server. Once connected, execute sp_setapprole on the open connection to
activate the app role. Only one connection is established.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"tgru" <tg**@devdex.com> wrote in message news:42********@127.0.0.1... Hello,

I am looking to have the developers at my company start to use
application roles to gain access to SQL. The basics seem simple, add the
role to the database, use sp_setapprole to activate it.

My question is most likely really simple as well...

How do you tell the application which SQL server you want to EXEC
sp_setapprole against?

i.e. do you need to establish an OLEDB connection before calling
sp_setapprole? Seems strange to have to establish a connection twice,
but I can't think of how else to do it. But I think I'm missing
something...

Thanks in advance!

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[tgru]

Thanks, that does help.

What credentials are used in the connection string? i.e. does a login
have to be created for the application as well? Or is the connection
string created with the application users credentials? Seems that you
would have to manage the individual users logins as well as the app role
at that point...

1.) establish oledb connection with SQL login credentials.
2.) call sp_setapprole with app role credentials.

I don't know why the original message in this thread keeps getting
posted every ten minutes!!! I only hit submit once, and then closed
that browser session!!
Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[tgru]

Thanks, that does help.

What credentials are used in the connection string? i.e. does a login
have to be created for the application as well? Or is the connection
string created with the application users credentials? Seems that you
would have to manage the individual users logins as well as the app role
at that point...

1.) establish oledb connection with SQL login credentials.
2.) call sp_setapprole with app role credentials.

I don't know why the original message in this thread keeps getting
posted every ten minutes!!! I only hit submit once, and then closed
that browser session!!
Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[tgru]

Thanks, that does help.

What credentials are used in the connection string? i.e. does a login
have to be created for the application as well? Or is the connection
string created with the application users credentials? Seems that you
would have to manage the individual users logins as well as the app role
at that point...

1.) establish oledb connection with SQL login credentials.
2.) call sp_setapprole with app role credentials.

I don't know why the original message in this thread keeps getting
posted every ten minutes!!! I only hit submit once, and then closed
that browser session!!
Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[tgru]

Thanks, that does help.

What credentials are used in the connection string? i.e. does a login
have to be created for the application as well? Or is the connection
string created with the application users credentials? Seems that you
would have to manage the individual users logins as well as the app role
at that point...

1.) establish oledb connection with SQL login credentials.
2.) call sp_setapprole with app role credentials.

I don't know why the original message in this thread keeps getting
posted every ten minutes!!! I only hit submit once, and then closed
that browser session!!
Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[tgru]

Thanks, that does help.

What credentials are used in the connection string? i.e. does a login
have to be created for the application as well? Or is the connection
string created with the application users credentials? Seems that you
would have to manage the individual users logins as well as the app role
at that point...

1.) establish oledb connection with SQL login credentials.
2.) call sp_setapprole with app role credentials.

I don't know why the original message in this thread keeps getting
posted every ten minutes!!! I only hit submit once, and then closed
that browser session!!
Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[tgru]

Thanks, that does help.

What credentials are used in the connection string? i.e. does a login
have to be created for the application as well? Or is the connection
string created with the application users credentials? Seems that you
would have to manage the individual users logins as well as the app role
at that point...

1.) establish oledb connection with SQL login credentials.
2.) call sp_setapprole with app role credentials.

I don't know why the original message in this thread keeps getting
posted every ten minutes!!! I only hit submit once, and then closed
that browser session!!
Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[tgru]

This sucks... It keeps re-posting my thread every ten minutes. I've
emailed support for the site about it. Sorry about that, not intentional
spamming!!

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dan Guzman]

> What credentials are used in the connection string?

One usually uses the normal user credentials for the initial connection in
order to validate the current user identity. You can either specify a
trusted connection (e.g. 'Integrated Security=SSPI') or the user's SQL login
(e.g. 'User Id=MyLogin;Password=MyPassword'). The specified account needs
to be granted access to SQL Server and your user database. However, no
object permissions need be granted to the user since you granted all
necessary permissions to the app role.

An advantage of app roles is that the login's original identity is
maintained (e.g. 'SELECT SUSER_SNAME()') but the security context of the app
role is used for database access. This works especially well with Windows
authentication because the user is not prompted to login and you can still
limit ad-hoc database access.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"tgru" <tg**@devdex.com> wrote in message news:42********@127.0.0.1...

Thanks, that does help.

What credentials are used in the connection string? i.e. does a login
have to be created for the application as well? Or is the connection
string created with the application users credentials? Seems that you
would have to manage the individual users logins as well as the app role
at that point...

1.) establish oledb connection with SQL login credentials.
2.) call sp_setapprole with app role credentials.

I don't know why the original message in this thread keeps getting
posted every ten minutes!!! I only hit submit once, and then closed
that browser session!!
Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!