SA: SQL Server with Access Academy Part V - Security with SQL Server and Access With Juan Soto
This is our final session of Access with SQL Server academy for 2023. We will discuss how to implement security with SQL Server and Access. There are two methods: SQL Server Security and Windows Security. Which one is best for your needs? Come find out!
Links Mentioned in the Presentation:
New Access Forever Site: https://www.accessforever.org/post/di...
MS Access Roadmap: https://www.microsoft.com/en-us/micro...
Upcoming AUG Access Pacific Meeting: Migrating tables using SSMA: https://accessusergroups.org/pacific/...
Microsoft Graph: https://developer.microsoft.com/en-us...
Table of Contents:
00:00 - Introduction
00:02 - Intro and Welcome By Juan Soto
00:12 - Topic: SQL Server Security
00:18 - But First, Some News
00:25 - New Bug Tracking Website Access Forever
01:02 - The new Web Browser Control is on Insider Channel
02:30 - The Last Chapter of SSA for the year
03:33 - LinkedIn Poll on SQL Server Security-Discussion
03:52 - SQL Server Security Models -- SSA and Windows Auth
04:27 - Active Directory Users, Groups and Role
05:03 - Application Security
05:42 - SQL Server Logins: Username and PWD
05:56 - SS Logins with Server on the internet
06:48 - Which is more Secure? Answer to the Poll Question
08:13 - Benefits of Active Directory
09:12 - Windows Groups Can be Mapped to SQL Server Roles
11:14 - Removing Inactive Employees from Windows Groups is Easy
11:52 - Inactive Employees lose Access to Applications through Windows Groups
13:57 - Benefits of SQL Server Security
15:57 - Using PowerAutomate to Manage Users in Azure Active Directory
26:14 - Add encrypted credentials
27:13 - A Movie Review -- Sneakers
28:34 - SQL Server Security for Road Warriors
28:58 - Time for Show and Tell -- Explore SQL Server Security using SSMS
29:20 - Security Node for Logins
29:36 - Create Logins at the Server Level or Database Level
29:45 - Server Roles
29:51 - All Users are in the Public Role
29:56 - Other Standard Server Roles, including sysadmin
30:10 - Logins Properties
30:22 - Sysadmin Role (or SA) Controls the Keys to the Kingdom for the Server
30:34 - Don't give sysadmin Rights to non-Admin Users
30:38 - User Mapping to Databases
30:52 - db_owner Role Controls the Keys to the Kingdom for the database
31:03 - Basic User Roles, db_datareader and db_datawriter
31:20 - Give Either db_datareader or db_datawriter, or both
31:57 - db_owner is usually the manager or company owner in smaller organizations without IT
32:25 - Configure Securables for Users
32:43 - Grant Rights to users
32:53 - Deny User Rights
33:26 - Brent Ozar SQL Server Expert Newsletter
36:14 - Mapping Users to Roles
36:29 - Add Users from Active Directory
36:44 - Add New Login
36:47 - Default Security is Windows Authentication, changeable to SQL Server Authentication
37:15 - Optional Configuration and Password Policy
38:14 - Change Password at Next Login Can be Problematic
38:27 - User MUST change password on Their Own
40:12 - Create Windows Auth Account
40:49 - Create Group, Assign Roles
41:02 - Windows Auth, no separate password, Through Windows Groups
41:37 - With Enterprise or Small Businesses, usually Windows Auth, With Hosted, usually SQL Server Auth
42:16 - Identify Users when they login
42:26 - Use API to Read Windows User Groups
43:15 - Future Technology: Microsoft Graph -- Get Acquainted
47:38 - Using Graph in Power Platform
48:19 - PowerApps Premium Connector
48:41 - Add a Guest User to MS365
49:12 - Power Platform Licensing Costs -- Dark Art
50:49 - The Discussion Widens beyond SQL Server Security
53:17 - Upcoming AUG Meetings
54:52 - Recommended Best Practices?
56:35 - Upcoming Session: Closing the Sale to Build a Successful Consultancy