473,320 Members | 1,744 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

using application roles

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!
Jul 23 '05 #1
9 3044
> 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!

Jul 23 '05 #2
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!
Jul 23 '05 #3
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!
Jul 23 '05 #4
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!
Jul 23 '05 #5
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!
Jul 23 '05 #6
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!
Jul 23 '05 #7
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!
Jul 23 '05 #8
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!
Jul 23 '05 #9
> 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!

Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: DCount17 | last post by:
I need to create a windows application (using C#) that creates/assigns and retrieves database roles on Analysis Services. While I can get the users from the "Members" table on SQL Server, I am...
2
by: YRao | last post by:
I am going to create intranet application using Windows Authentication using C# asp.net I am having following problem: 1 setting windows Authentication, it will validate for all users, user...
4
by: tommy | last post by:
hello everbody, i write a little asp-application with forms-authentication. i copy my aspx-files with web.config to my webspace and i get the error above... i tried to set the...
5
by: Jonathan Allen | last post by:
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 =...
3
by: Carl M. | last post by:
Okay, I'm 18 hours into this now and am at a loss. What I've done is create an application on my local workstation using VWD Express using the membership/roles provider and SQL Server Express...
1
by: Screenbert | last post by:
After finding nothing anywhere in google I am posting this so everyone can benefit by it. The formating is not pretty since I copied it from my word document, but you should benefit by it. ...
0
by: screenbert | last post by:
Managing DHCP Servers using C# They said it was impossible. It couldn't be done. But you can in fact manage DHCP servers using C#. This includes creating and deleting Scopes, SuperScopes,...
6
by: Matt Adamson | last post by:
Guys, I'm unsure how to use windows authentication in an intranet application. I'd like to user existing windows account to identify users however the issue I have is how to then add settings to...
2
by: CuteDeveloper | last post by:
Hi everybody! I can not understand how to use SQL2005 Appicatioin Roles (AR) with ASP.NET 2.0? The tricky thing here is that you have to set up your connection string to use integrated...
2
by: Vincent | last post by:
Hi, When the application doesn't use Roles, this configuration (web.config) works: <configuration> <connectionStrings> <clear/> <add name="myconn" connectionString="Data...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.