September 7, 2008 at 12:50 pm
First of all go back until 2 days ago and reread my posting.
You do NOT assign yourself to the 'donuttin role - keep your current role which I assume is dba or dbo. It is all the other users who will be using the application who are assigned to the donuttin role. (Again some emphasis the, donuttin role is created in the DB which the application is accessing)
Have the application log in using Windows authentication. I assume it is what the application is now doing.
Immediate after the application logs in to the server and opens the database (I normally check for this in the first forms, form open subroutine.)
Following what you have stated:
My understanding is simply this:
- Open application
- App gains access to DB
- DB uses WAM to verify that the user is valid
The above can be accomplished using your existing connection string that exists in the application code.
Then in your application code:
The application invokes the command:
EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM'
, @fCreateCookie = true, @cookie = @cookie OUTPUT;
-- The application role is now active.
SELECT USER_NAME();
-- This will return the name of the application role, Sales11 which you had already created in the database to be accessed and 'fdsd896#gfdbfdkjgh700mM' is the password
The user now has only those permissions granted to the application role (in this example: Sales11).
When closing the application, immediately before the close DB command your code should then:
EXEC sp_unsetapprole @cookie;
You of course must declare in your code the variables @fCreateCookie, and @cookie. And the variable @cookie must be available to both the routine which invokes the set app role and the routine which un sets the app role. In VB terms this would be a varable defined as PUBLIC
Now again if you are using SQL 2005 I urge you to download Books On Line for 2005 and follow the examples given therein for Application Roles. If your server is SQL 2000 then the commands will be slightly different for invoking the App role and the sp_unsetapprole will NOT be available.
September 7, 2008 at 1:00 pm
IMO it is best to switch the approle immediate after the connect.
This way you'll not forget to code it before you execute any db requests.
Keep in mind, approles are not reflected in the windows connection pool,
that's why IMO the switch should be performed asap.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 7, 2008 at 2:00 pm
IMO it is best to switch the approle immediate after the connect.
ALZDBA - that is exactly the flow specified in my posting.
This way you'll not forget to code it before you execute any db requests.
Here I take it that you do NOT consider invoking the sp_setapprole procedure to be a DB request, but rather are classifying Create, Request(Select), Update, Delete as
db requests.
September 7, 2008 at 2:02 pm
BitBucket, Thanks! I'm downloading BOL 2005. It was my Sam 21 Days book (for SS2k), i think, that says i have to add myself to the db role. I didn't understand why. But ok. Its better if i don't have to.
In your previous post, some months ago, somehow i didn't understand what you were saying.
Check "C:\Program Files\Microsoft SQL Server\80\Tools\Books\adosql.chm" - part of a complete install of SQL 2000, and a fantastic resource for using ADO to communicate with SQL Server.
I'm going to see if i can find the BOL 2005 examples and I'll let you know what i find out.
(I dont tend to find BOL very easy to use although some people seem to swear by it.)
Its almost finished installing. I meant download.
September 7, 2008 at 2:11 pm
Jacob Pressures (9/7/2008)
Every time a new user comes into the company, will I have to add them also to the DB role? (just seems crazy)
Yes, well someone will have to. This is a large part of what everyday security administration actually is, though it is usually done by someone with a job title like "Account Administration", for the Windows stuff anyway.
Now how do i configure the connection string to send approle name and password?
You don't, your application must execute the sp_setapprole command with the password. Please review our previous posts, we have already been over this a couple of times.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 7, 2008 at 2:19 pm
Check "C:\Program Files\Microsoft SQL Server\80\Tools\Books\adosql.chm" - part of a complete install of SQL 2000, and a fantastic resource for using ADO to communicate with SQL Server.
adosql.chm is a help file for using ADO (ActiveX Data Objects) with SQL server -- was part of a full install of SQL 2000 -- has a lot of code samples which can literally be cut and pasted into your code.
BOL - agreed not the easiest item to read - sometimes have to hop from page to page and then back again to get it to make sense, but heck is better than nothing.
Additional sites for some good assistance are:
Technet: http://technet.microsoft.com/en-us/default.aspx
MSDN: http://msdn.microsoft.com/en-us/sqlserver/default.aspx
both have search windows in upper right hand corner of the home page which makes it very handy resource.
Sorry had to leave to handle some personal business - wife wants to pick pant colors for the new house.
Jacob -- sounds as if you might be inheriting some aspects of the network administration work as well as work performed by the network admin re the SQL Server.
Every time a new user comes into the company, will I have to add them also to the DB role? (just seems crazy)
As RBarryYoung has stated:
Yes, well someone will have to. This is a large part of what
everyday security administration actually is, though it is usually done
by someone with a job title like "Account Administration", for the
Windows stuff anyway.
Equally important and often overlooked -- for those people whose work assignment may have changed or those who have left the company either voluntarily or un-voluntarily must be removed from the authorized logins and removed from the roles. If you are using Windows authorization removing that pretty much keeps them from accessing the Server. Then you have to remove that login from the role. Forgetting that little bit of housekeeping can eventually result is one heck of a mess.
.
September 7, 2008 at 7:17 pm
Now how do i configure the connection string to send approle name and password?
You don't, your application must execute the sp_setapprole command with the password. Please review our previous posts, we have already been over this a couple of times.
Thanks Berry, I'm going to go back and reread all the post and see what i find.
I think i have a better understanding of the situation, but maybe i don't. What you said here sounds a bit like a contradiction to me. FYI.
Don't worry about replying yet. I do appreciate it though. I need some time to look all this stuff up and read it.
This is what i currently understand
Database Side
- Create approle with password
- Create database role
- Assign users to database role
- Deny all permissions unless some are necessary
(this is off the top of my head. If i miss something I will come back and complete the sketch.)
Application Side (fizzy part)
- Connect to the database using connection string
- Execute the sp_setapprole storedprocedure with password some kind of way.
- end the approle session
- end connection
What happens
- User logins in using WAM
- Opens application
- Application does above
- SQL Server authenticates and switches from DB Role Privileges to Approle
- DB role privileges are suspended.
- App gathers information
- Ends it approle session
- Ends connection
Now if the issue is with the connection string part, I don't know. NOw my jargon might be off, but anything that i pass to the ADO object has been called the "connection string." It may be that once the connect is open all i need to do is let it execute the code as provided. I've never worked with stored procedures before so this process is foreign to me. The query is placed into a string and attached to the ADO object. it produces the recordset, everything, including the recordset code, has all be called the "connection string" because i don't know what else to call it. In code i just assign it to the SQLstr.
Thanks very much for your patience guys. You've been unbelievably helpful. I just need some time to read and digest all of this.
Best wishes.
September 10, 2008 at 9:24 am
I'm in DatabaseRole Object in BOL2005. it says, "This feature will be REMOVED in a future version of MS SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."
WHAT?
This is soooo.... confusing.
September 10, 2008 at 1:53 pm
OK guys. To be sure I STARTED FROM MY FIRST POST all the way down and then reread yet again the post from the previous two days.
Again, i think i understand the meat of this thingy. There is so much involved that it is taking me some time to put all this stuff in perspective. A lot has been said and at different times it can be confusing.
Now i want to put into context what I've learned today.
1) Approles are going away in the newest version of SS. Correct?
(I did not know this. I did know there was some alternative.)
2) Does the new way or alternative way require i use storedprocedures?
I don't have enough time to shift gears at the point. Since I have a grasp of this, i will use approles and make changes later.
Barry said on 7-21-08
Jacob Pressures (7/21/2008)
--------------------------------------------------------------------------------
I'm using VB6 with ADO.
In that case, I'd suggest that you stick with AppRoles. When combined with good stored procedure security, they provide very good ROI, security-wise. Plus, I am not sure how much support for ceritificates and exe-signing there is prior to .Net.
What are certificates? You mentioned this several days earlier but i didn't quite know what you meant. Sounds to me like the "alternative way" instead of the approle IS certificates and they may be a problem if I'm not using storeprocedures and or pre-.NET code.
(I think I've heard of certificates before. Is this a security feature between two entities for transferring information? Only those entities that have the key can unlock or decypher the encrypted message? Something like that.)
3) Would it be fair to oversimplify the database role/approle as a rigging to get something to work a certain way? Might that explain why the relationship does not seem very intuitive?
4) Is #3 and the security issues mentioned before the reason why approles are being phased out?
Ok, if all the above is true, that helps me to understand where i am in the stream of time. If the alternative way is too difficult to implement given my short time and my current code situation, then I need to stick with approles with the intentions of moving towards storedprocedures and away from approles if possible with my current code.
I still have not read the BOL examples yet. But I'm getting there.
This has been a journey. It is also one of those things easier explained over the phone.
Oh, here is another issue. Just remembered.
BitBucket you said this:
Have the application log in using Windows authentication. I assume it is what the application is now doing.
I took for granted that I understood what you meant. But a more careful reading may not be what i thought.
Barry said something similar on 7-19-08
0) Your users just login to Windows as they normally do, then run the app. For bad apps, they will also have to login to the app.
Ok, my database is configured to use WAM. But my app, according to Barry, is a bad app. My app does not automatically know who is logged in based on Windows Authentication. My users have to fill in their name, title and other info. Barry said it was a bad app, but he didn't say that it was important. Since a careful reading of your statement repeats this same thought-- it seems, I thougth it was important to meantion.
I am using WAM between the workstation and the DB. But my app does not use this method.
My next goal is to start reading these examples in code and implementing this stuff.
Thanks!
September 10, 2008 at 2:29 pm
Jacob did you get my Private Message to you.
First of all the App user does NOT have to login to SQL Server and/or the database, as long as the database is configured to use Windows Authorization, and the user has log in permission for the Server and DB and the correct connection string is in the App. My demo App itself will generate the proper connection string which in turn you can cut and paste into your App. I have attached a file in zip format here. Included in the file is the small simple program written in VB6, which I hopefully have fully documented, plus a word Doc with some additional explanation of what the program does, along with jpg illustrations. Read all the code in Form1 General section to be sure your project contains the proper references. Then compile and run in Debug mode. It will log you into SQL Server and open the DB of your choice, without you previously logging in to SQL prior to running the App It will display the proper login string in a window in the form.... Then you can expand the simple App to invoke the App role. Then after you are satisfied with how it is done you can copy the code or place similiar code in your Application. If you are unsure you can install the demo App on a users machine where the user does NOT have Dbo or DBA authorization and test again.
September 10, 2008 at 2:41 pm
How are you thanks! Yes, I did get your PM. I also replied with my yahoo address with the same handle/username. JacobP....@yahoo. I was waiting on you and wondering why you hadn't responded. That explains why. 😀
I don't recall ever saying that one has to log into SS2k or SS2k5. I understand WAM quite well.
Thanks for the code. I really appreciate it. I will be studying it for the next few days until i get it running.
September 10, 2008 at 3:11 pm
Jacob, well I again checked my incoming PM box, nothing, oh well will leave this subject (forum) open so I will recieve a message saying that some one posted to this forum. This way if you do have any questions post to the forum and I will try to help clear up any mystery I may have included in my code.
September 10, 2008 at 5:58 pm
Jacob Pressures (9/10/2008)
I'm in DatabaseRole Object in BOL2005. it says, "This feature will be REMOVED in a future version of MS SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."
That's because THAT is a SQL-DMO entry in BOL. SQL-DMO was superseded by SMO in 2005, so DMO is obsolete and no longer needed. Thus every entry under the DMO section has that warning because DMO will be removed in the future (not Database Roles).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 10, 2008 at 7:12 pm
Jacob Pressures (9/10/2008)
1) Approles are going away in the newest version of SS. Correct?(I did not know this. I did know there was some alternative.)
No, I know of no place where it says this. (see my note above)
2) Does the new way or alternative way require i use storedprocedures?
Certificates might, but I don't think so. I have always used them with stored procedures, which is why I am not sure.
Barry said on 7-21-08
Jacob Pressures (7/21/2008)
--------------------------------------------------------------------------------
I'm using VB6 with ADO.
In that case, I'd suggest that you stick with AppRoles. When combined with good stored procedure security, they provide very good ROI, security-wise. Plus, I am not sure how much support for certificates and exe-signing there is prior to .Net.
What are certificates? You mentioned this several days earlier but i didn't quite know what you meant. Sounds to me like the "alternative way" instead of the approle IS certificates and they may be a problem if I'm not using storeprocedures and or pre-.NET code.
They are more complicated, but much more secure. They are really meant to work with .Net clients. I am sure that there must be a way for COM/VB6/ADO clients to use them, but it is certainly much more difficult for you than AppRoles (which themselves ar enot yet working for you).
(I think I've heard of certificates before. Is this a security feature between two entities for transferring information? Only those entities that have the key can unlock or decypher the encrypted message? Something like that.)
Yes, but in reverse: the encryption process is used as a "signature" to prove that you have the certificate, without actually having to pass the certificate itself.
3) Would it be fair to oversimplify the database role/approle as a rigging to get something to work a certain way? Might that explain why the relationship does not seem very intuitive?
Sorry, I do not understand this question.
4) Is #3 and the security issues mentioned before the reason why approles are being phased out?
I do not know that AppRoles are being phased out, however, they are not the best practice, because they have issues with cleartext key(password) transmittal, lack of good built-in encryption (ODBC encrypt is not truly secure), key(password) distribution and updating etc. If you were to solve all of those problems, you would have Certificates.
Thus AppRoles can be seen as an earlier, simpler, but less secure version of Certificates. But AppRoles are still much better than not having either AppRoles or Certificates. And their advantages for you are: 1) simple to use, 2) easy to set up, and 3) no dependencies on VS.net, ADO.net or .Net framework.
You should probably consider Certificates for your app when you are ready to upgrade it to .Net.
Ok, if all the above is true, that helps me to understand where i am in the stream of time. If the alternative way is too difficult to implement given my short time and my current code situation, then I need to stick with approles ...
Yes.
... with the intentions of moving towards storedprocedures and away from approles if possible with my current code.
No.
Stored procedures should not be seen as a substitute for AppRoles. They can be used as a complementary security facility along with AppRoles. AppRoles and Stored Procedures can each give you a seperate layer of security, and they can be used together to foster a key security practice: "Security-In-Depth".
Certificates are the replacement for AppRoles, not Stored Procedures. It would not make sense to use AppRoles and Certificates together because they really address the same layers of security.
Ok, my database is configured to use WAM. But my app, according to Barry, is a bad app. My app does not automatically know who is logged in based on Windows Authentication. My users have to fill in their name, title and other info. Barry said it was a bad app, but he didn't say that it was important. Since a careful reading of your statement repeats this same thought-- it seems, I thougth it was important to meantion.
Right, if you are already doing it this way, it is not necessarily a big deal, however, best practice is for the App to use the WAM instead of it's own authorization facility. This is because WAM is almost certainly better (more secure) than anything that you could write yourself, so the best practice is for the App to just piggyback off Windows.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 11, 2008 at 8:48 am
Ok thanks! Around the world and back again.
I looked up approle in the search section. That brought up the DMO stuff. So that was the problem. This time I typed application role and everything was fine.
To clear up the confusion, I'm operating on the DB with direct SQL statements and not with storedprocedures. So I plan to rewrite these statements using storedprocedures. Once I upgrade to .NET i may use certificates in the place of approles. I did not mean storedprocedures in the place of approles.
I have learned so much from this thread. Thanks!
But why has this been so hard? I thought connecting to different DBMS was supposed to be quite seamless.
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply