Has anyone ever used an Application Role for their app?
-
I'm using vb6 and i'm trying to create a connection string using an
application role.
If this is possible.
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.
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?
Thanks!
-
Hi Jacob,
I use AppRoles, but I'm not sure what you are asking here. AppRoles are very useful, because the user may access the database ONLY through your app, and can't get access to the SQL Server through EM, MS Access, etc.
When you create your app, you must connect to SQL Server normally using either Windows authentication or SQL Server Authentication--it does not matter for app roles. You can use any connection string that ordinarily works for you.
The initial connection to your app generally requires that the user has his own unique login and pw that is not stored as text in the database. This will be either using WIndows authentication or SQL Server authentication. This is the entrance key to your app.
The user should very minimal permissions, just sufficient for starting your app, and generally with no editing rights at all.
Once the user is allowed in your app, and one or more cached connections have been opened, you then assign an application role to whatever connections he is using. AppRoles are always "applied" to previously opened connections. You must apply the AppRole(s) to every connection that the user uses in your app.
The password for the AppRole is generally stored encrypted in the database, in the app itself, or in a file somewhere. If you store the PW in the database (encrypted, NOT using SQL Server encryption, but something better), make sure that all your users have SELECT or EXEC access to a view or SP that returns the encrypted PW.
To apply an AppRole to a connection, you should look up sp_setapprole in BOL. All you need to do is execute this SP on the connections in your app, and all the users permission will be completely converted to the AppRole permissions, but only within your app's connections. Make sure you use the ENCRYPT option, and do NOT type the actual password into your app's code. Generate the decrypted PW from a custom private function in your app code. It is also advisable to use a secure encrypted network connection such as SSL, etc.
I hope that helps to dispel some of the confusion...
-
thanks it does help except i'm having problems encrypting my password.
I get the following error:
[Microsoft] [ODBC SQL Server Driver] Syntax error or access violation
-
I looked at your other post, and and can't see anything wrong with it. If you are just testing it with a plain text PW, it should work.
You should note, however, that you can't appy an AppRole more than once to any given connection - you will get an error. Perhaps that is the problem.
Other than that, I would guess you have a typo somewhere. That N'Password format is often a problem as well. You might want to check what the statement looks like in the SQL Profiler.
-
Thanks very much man for the info!
"You should note, however, that you can't appy an AppRole more than once to any given connection - you will get an error. "
If i try to drop it, it drops with no problem. So i assume that it should then in the same "batch" be able to create an encrypted password.
I just tried using a new name, but i still get the same error on the encryption.
In the meantime I've been trying to grant permissions.
I want to
grant select, insert, update, references on [All MY Tables but don't now how] to faelogin
I can do it table by table but not on all the tables. Do i have to list each table out?
I will do it this way until i turn all my queries into stored procedures.
Thanks!
-
I figured it out!
I found the answer here http://www.eggheadcafe.com/forumarchives/SQLServersecurity/Jun2005/post23406753.asp
it appears that certain characters (such as periods on other characters) don't work well with the encrypt function.
This really angers me! i wish i could have been told that in BOL!
-
The period is not listed on the link but once i take it out of my password, THERE ARE NO ERRORS!
Here is a list of other characters that apparently cannot be used. I was able to use the underscore. The period is not listed, but it doesn't work. The dash is not listed but it won't accept it either. It will accept a dollar sign.
/* Unallowed characters:
! = 33
( = 40
) = 41
, = 40
* = 42
; = 59
? = 63
@ = 64
[ = 91
] = 93
{ = 123
} = 125
*/
-
These ascii codes might be a more complete list.
The password characters are not allowed for or the canonical ENCRYPT =
function does not work with characters with the following ASCII codes:
(33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,
162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,
190,191,215,247)
-
Wow, I got zinged by the same issue when I started with AppRoles! There's also a length limit to the PW of ? about 64 characters, I think. Since I generate random long passwords for AppRoles, that was an issue for me.
BTW, I don't think you can use them on linked servers either, but fortunately, that's not a problem for me.
AppRoles are a great, but vastly underused solution to some major problems, so it's good to hear that things are starting to work for you.
Good luck....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply