Connection String using application roles

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Connection String using application roles

Author

Topic

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-09-27 : 15:53:00

I don't know what the string should look like or whether i have put everything in place.

I'm using Windows Authentication Mode. However I created a Application Role and password for my app. Should i use this to connect to the db so i don't have to use a userid? I shouldn't need a windows login to use an application.

According to my book: "After the application has enabled and application role, all permissions of the user are suspended, and only the permissions of the role are enforced." Then it says later: "The best part is that all activity is still audited with the users' login information."

Microsoft said at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg01_0ahx.asp :
"Use the Integrated Security keyword, set to a value of SSPI, to specify Windows Authentication (recommended),
or
use the User ID and Password connection properties to specify SQL Server Authentication.

Security Note When possible, use Windows Authentication. If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32® crypto API. For more information, see "The Crypto API Function" in the MSDN® Library at this Microsoft Web site."

I don't can't make sense of both of these statements. How do they work together or do they contradict? What should i do?

Here is my connection String
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=ServerName

I'm able to get it to work with my login using Windows Authenticity, but everyone can't use my login name. I can't set permissions for all users either.

Thanks!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-27 : 17:33:38

It's from SQL Server 7.0, but I believe this article is still accurate:

http://www.windowsitpro.com/Article/ArticleID/20534/20534.html

Here's another one:

http://msdn2.microsoft.com/en-us/library/8xx3tyca

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-28 : 01:59:13

logins are different from approles

the login is used to gain access to the server/database, the approle is used to invoke the permission set for that approle

whatever your login setting is you still need to set the approle

for windows authentication, your connection string is ok

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=ServerName

now you have access, what you need to do is to set the approle using your connection above
--execute the query below
exec sp_setapprole @rolename = 'role' ,@password ={Encrypt N 'password'}

the permissions now for the objects shall depend on the permissions you've set for the approle.

HTH

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources