5-302 1 2

This was a very good session that covered all of the security infrastructure in detail.   The topic was a little dry, but it was kept interesting.  The slides were a little funny.  Interspersed about every other page was a slide with the logon/authentication process.  You got a real good idea of where most of the security questions came from.

One of the things I missed in the documentation and I'm sure many others did as well concerned standard security.  You can only choose from mixed or integrated security.   Integrated security is good.  Standard security is bad.  No work is being done on standard security.  It is in version 7.0 for backwards compatibility.   It will be in the next release and removed from the one after that.  If you are not running integrated security, better make plans to switch over.

Your authorization is based upon group membership.  Suids no longer exist.   SQL Server now stores the SID in the system tables.  The proper table is sysxlogins.  Syslogins exists for backward compatibility and is simply a view.   Do NOT use the suid you get from querying this table.  It is simply a random number generated to make you feel better.  Authentication is accomplished using SSPI.   (Don't ask, I don't understand it.)  Delegation is not available in version 7.0, even with Windows 2000.  However, you can do IP encryption.

The slide that kept reoccurring concerned the security model.  The important protocols to remember are named pipes and multi-protocol.  These give the most security problems of any other protocol.  The steps are as follows:
1.  Network connect request to ipc$
2.  Connect to SQL Server computer
3.  Login authentication request to SQL Server
4.  Establish credentials
5.  Switch database context and authorize access
6.  Establish the database context
7.  Attempt to perform an action
8.  Verify permissions for actions within the database
The biggest thing to remember is that to must authenticate to NT first with named pipes or multi-protocol.  No NT authentication, no connection.  Named pipes requires connection to the ipc$ share in order to work.  If you do not have an NT account, access will be denied.  The only exception is if the guest account is enabled which is not recommended.  SQL Server also holds credentials for the account once it is logged on.  You can enter the following: net use \\servername\ipc$ D to delete the credentials and reset.

With integrated security, you can assign access to both users and groups.  This can create some conflicts.  SQL Server will use the user credentials before the group credentials.  All security is an implicit deny.  Unless an administrator explicitly grants a permission, you will not have access.  Just like in NT, all permissions are cumulative across all roles that you are in.  The only exception is a deny.  If you are a member of a role that is denied access to an object, you will not have access even if another role grants you access.  Remember, deny overrules everything.  One thing to never do is to deny permissions to the domain users group.   This will effectively lock everyone out of the server including the administrator.   How to get back in?  You have to hack the security settings in the registry to regain access.