There are two levels of access control within any SF application that is using RBS:
1. Control of access to the database(s) via control of the connection strings, including any user names/passwords. You can connect to the database via a domain login or via SQL authentication. This part has nothing to do with RBS and is common to all SF applications. This controls access to the database, but has nothing to do with control to elements within the application (like forms, BO CRUD access or fields in a BO...that is RBS in #2). This is setup in AppMain in the SetDataSources () method. You can either use the ConnectionStringManager to build the connection string if the model for your app is that the users know how to connect to the database or you can manage the connection string manually, using the DataSources.Add() method. Typically you'd use this when the users don't know how to access the database. There is more information about both of these in the help files.
2. Control of access to forms, to data via BOs and to fields within BOs using the RBS. There are several steps to using RBS (all outlined in the help files). First you'd use the Security Dialog to setup permissions. These are simply named entities that can be assigned to users that can be programmed against to determines a user's access to some element of the application or to data via the application. E.g. you might have a PatientRecord permission that controls access to a patientrecord in the application. Once the permission is defined within the RBS system, you can use it to control access. They can be used to control if a form can be opened, if a user can add, update or delete data in a BO or all the way down to the field level in a BO. You can also program against the permissions to control things like menus, toolbars, hiding/showing elements on forms based on permissions. The application users who are responsible for controlling access to their app are then given access to the security dialog. They can then add/edit/remove users, define password and other security policies, create roles (sets of permissions) and assign roles and permissions to users. The final piece that bit a lot of us when starting to use the RBS is that when you develop security using RBS, you typically are storing the data in your StrataFrame database. You then have to deploy that data to the sql server you'll use in test/production. The DDT makes this very easy, but is not required.
So, give these two control mechanisms and your questions, here are a couple of scenarios (there are many more, as this is quite a flexible system).
The first scenario is kind of inline with what I'm guessing you were thinking when you asked about SQL Server security integration (kind of). You could allow access to SQL Server within your app via individual SQL Server user names and passwords and/or domain user account quite easily using the ConnectionStringManager. The users would have to know where the SQL Server is, and have a login on the SQL Server. You wouldn't use RBS at all (more on this in a bit), rather it just controls who is getting to SQL server, with no control else where in the app at all (unless you programed it yourself...more on that too).
There are sooooooooooo many issues with this approach, if you want to secure both your app and your data. First, the ConnectionStringManager caches the connection info in an encrypted file in Documents and Settings. It only prompts the user for the connection information if it doesn't find it in this file. Which means that on your shared workstations, you have to do some coding to make sure every user has to enter the connection info, like deleting this file right after it is created or something. Second, this is not actually a login at all, thus you don't have a user within the app, you have a connection string to connect to the database. You could pull the user name out, but then you come to the next problem: no application level security. While you've controlled access to the database, and with some code you can get the user name from the connection string, there is no mechanism to use that user name to determine if the user has access to any part of your application...i.e. you'd have to build your own security system, not a trivial exercise at all. Your data is also not as secure and/or your app is hard to manage because you have to manage users on SQL Server, which means you have to either open it up for the users to add logins (sysadmin privileges I believe) or you have to burden some dba to manage your users. Also, the server has to be know to all the users, which means they now know where to go to attack it. I'm probably forgetting a bunch of issues....this is just not a good idea unless the app is such that the user actuall is in control of the database, such as a single user or small group app were security really isn't a concern (they just lock the door at night, so people the don't know can't get in
)
The second scenario is what I'm talking about as solving many of the issues you mentioned. In this case you control access to the database by having only a single SQL Server account that can access your database. You use the DataSources.Add() method within SetDataSources() in AppMain.vb. This is setup when the app is installed and controlled by whoever controls the db. There are a zillion ways to handle this (which I'm not going to go into now). Then you use RBS to secure the app. This provides authentication, a user object to work with, and hooks to determine a users permission. You can authenticate against a domain if you want (though you still have to maintain the users in your apps security tables, so RBS knows what permissions have been assigned). As the developer you define the permissions needed to access any object in the application and to access the data by configuring the BOs with permissions. The users with the proper permissions (defined by you) can manage users, including adding, editing and deactivating them as well as assigning them roles and permissions.
I hope this clears up some of your confusion. RBS is a big subject. I had lots of pain getting my head wrapped around it, but that is actually more a function of the nature of security than of any security system in general. If you haven't already, I'd suggest you do a test project to try this stuff out.