Transcription
Advanced On-Prem SSRS 2017for Non-AD UsersDr. Subramani ParamasivamMVP & Microsoft Certified TrainerDAGEOP, UK
A Big Thanks to Our Sponsors
About the SpeakerLocal & User Group LeaderDr. SubraMANI ParamasivamPhD., MVP, MCT, MCSE (x2), MCITP (x2), MCP, MCTS (x3), MCSACEO, Principal Solutions ArchitectAmbassador@ DAGEOP QL-MAN-LTD/http://www.youtube.com/user/YourSQLMAN
All possible solutions in one screenFBA Logon PageDomain ControllerAuthenticationComponentsSharePoint ServerClaimsProviderRelying PartyReport ServerEnd User SystemFirewallWeb Application ProxyReverse Proxy NetScalerEUM & ADFSLoad Balancing NetScalerSQL Database
Method 1FBA Logon PageDomain ControllerAuthenticationComponentsSharePoint ServerClaimsProviderRelying PartyReport ServerEnd User SystemFirewallWeb Application ProxyReverse Proxy NetScalerEUM & ADFSLoad Balancing NetScalerSQL Database
Method 2FBA Logon PageEffective solution for RLSDomain ControllerAuthenticationComponentsSharePoint ServerClaimsProviderRelying PartyReport ServerEnd User SystemFirewallWeb Application ProxyReverse Proxy NetScalerEUM & ADFSLoad Balancing NetScalerSQL Database
Method 3FBA Logon PageDomain ControllerAuthenticationComponentsSharePoint ServerClaimsProviderRelying PartyReport ServerEnd User SystemFirewallWeb Application ProxyReverse Proxy NetScalerEUM & ADFSLoad Balancing NetScalerSQL Database
Method 1
FBA Logon PageDomain ControllerAuthenticationComponentsSharePoint ServerClaimsProviderRelying PartyReport ServerEnd User SystemFirewallWeb Application ProxyReverse Proxy NetScalerEUM & ADFSLoad Balancing NetScalerSQL Database
Form Based AuthenticationIntroductionApplicationSSRSCustom Authentication.NET ApplicationExternal / Other DomainAccessSingle-Sign on (SSO)IAuthenticationExtension2 (Interface)Access to Report ServerIntranet & ExtranetAssembliesAccess via customapplication
Form Based AuthenticationSSRSRSReportServer.configWindows IntegratedSecurity Authentication AuthenticationTypes Custom / /AuthenticationTypes EnableAuthPersistence true /EnableAuthPersistence /Authentication Web.config in Report ServerBasic Authentication authentication mode "Forms" / identity impersonate "false" / Authenticate viaSharePointWeb.config in Report Manager authentication mode "Forms" / identity impersonate "false" /
Method 2
Effective for RLSFBA Logon PageDomain ControllerAuthenticationComponentsSharePoint ServerClaimsProviderRelying PartyReport ServerEnd User SystemFirewallWeb Application ProxyReverse Proxy NetScalerEUM & ADFSLoad Balancing NetScalerSQL Database
Entities - WANKENetScalerWAP - Reverse PolicyNetScaler ADC is an Application Delivery Controllertool, that improves the delivery speed and quality ofapplications to end users. It also provides flexibledelivery services for traditional, containerized andmicro service applications from your data center orany cloudFirst introduced in Windows Server 2012, WebApplication Proxy (WAP)provides reverse proxyfunctionality for web applications inside yourcorporate network to allow users on any deviceto access them from outside the corporatenetworkADFSAD FS is a standards-based service thatallows the secure sharing of identityinformation between trusted businesspartners (known as a federation) across anextranet. When a user needs to access aWeb application from one of its federationpartners, the user's own organization isresponsible for authenticating the userand providing identity information in theform of "claims" to the partner that hoststhe Web application.KerberosKerberos is a secure protocol that grantsauthentication tickets to the Key Distribution Center(KDC) with valid user credentials and SPN (ServicePrincipal Name). Kerberos is the preferredauthentication type for SharePoint because it is faster,more secure, and reduces the number of errors youcan get with username and passwords than NTLM.EUMAuto login for corporate users.Collaborate with external users
WAP First introduced in Windows Server 2012 Reverse proxy functionality for web applications inside your corporatenetwork to allow users on any device to access them from outside thecorporate network. WAP pre-authenticates access to web applications using ActiveDirectory Federation Services (ADFS), and also functions as an ADFSproxy.
WAP
AD FSStepsFederation ServiceCentralized FederatedPartner ManagementClaimmappingsRegister AD FS Web AgentReference SSO AssembliesIdentity SharingWeb Services (WS)-*interoperabilityWeb SingleSign OnObtain SSO Identity ObjectVerify AuthenticationCreate Client ContextClaims BasedAuthenticationAdfs.mscExtensible ArchitectureVerify AuthenticationRetrieve Claims
AD FS
NetScalerApplicationIntroduction - ADCCiTRiXMonitoringServerTraditional . Real-Time & edHighAvailabilityHigh Performance –Mobile NetworksMicro Service
NetScaler
NetScaler
Kerberos Authentication Windows Server 2003implements theKerberos V5 protocol asa security supportprovider (SSP) The Kerberos KeyDistribution Center(KDC) uses the domain’sActive Directory servicedatabase as its securityaccount database. ActiveDirectory is required fordefault NTLM andKerberosimplementations.
Kerberos Authentication
EUMExtranet UserManagementExtranet AccessIntegrate with SharePoint
Shadow Account Normal user accounts It is an identity in secondary forests ADFS may be beneficial to use shadow account in some situations
Architecture ExplainedADFS configuration as Pre-AuthenticationWeb RoleCreationPublish Web Application& Host(Map the Ips)External URL setupEnable theEUM ClaimsUpdate the ClaimRule to replacewith “if” logic (ifrequired)Certificates Create certificates Certificate enrollment Assign the certificate
SSRS & Netscalar ConfigConfigure SSRS to useInstalled certificatesUpdate RSReportserver.config file to useWindows KerberosWindows NegotiateContent Switching Virtual ServerNetScalerContent Switching policyLoad Balancing Service
SPN, Kerberos & DelegationCreate domain user Service Principal Name (SPN)NetBIOS setspn –A MSSQLSvc/ SQL Server computer name :1433 Domain\Account FQDN setspn -A MSSQLSvc/ SQL Server FQDN :1433 Domain\Account Verify the registered SPNsetspn –L domain\SQL Service Account Mutual Authentication Secure Authentication Tickets Integrated AuthenticationMicrosoft Kerberos Configuration Manager for SQLServer is a diagnostic tool, that helps troubleshootKerberos related connectivity issues with SQL Server SQL Server Reporting Services SQL Server Analysis Services
Sharepoint – Identity server Trusted Identity Provider SharePoint Relying Party in Identity Server SharePoint Role Claim – Allow Access Configuration with ADFS– Identity Provider– Relying Party
ScenarioHQ ManagerAD Accountalexander@sqlbits.comSB HQ 001martin@sqlbits.comSB HQ 001DivisionManagerAD Accountsam@sqlbits.comSB DM 001antony@sqlbits.comSB DM 001Store ManagerAD Accountalan@sqlbits.comSB SM 001DatasetProfile Tablesuser name()SecurityPolicy &FunctionRLS Config@DB level
Method 3
FBA Logon PageDomain ControllerAuthenticationComponentsSharePoint ServerClaimsProviderRelying PartyReport ServerEnd User SystemFirewallWeb Application ProxyReverse Proxy NetScalerEUM & ADFSLoad Balancing NetScalerSQL Database
ScenarioSharePointUser MappingUser NameAD AccountShadow AD AccountCategorizationAlexAlexander@SQLBITS.comSB HQ 001Head quartersMartinMartin@SQLBITS.comSB HQ 001Head quartersSamSam@SQLBITS.comSB DM 001Division ManagerSB DM 002Division ManagerSB SM 001Store m
EUM AlternateExtranet Collaboration Manager(ExCM) for SharePoint – premierpointsolutionsExtradium for SharePoint - riolinx
More info
Virtual Machine 1 – Domain ControllerVirtual Machine 2Virtual Network SharePoint Server 2013 SQL Server 2012 SQL Server 2016 SQL Server 2016 SSRS 2017Mobile Reports
EUM Admin PageRLS ReportVirtual NetworkEnd User SharePoint SSRS ReportEUM Landing PageSSRS Native Mode – Report Viewer
Before SQL 2017 – SharePoint Integration ModeSQL 2017 – Native ModeNo SharePoint Integration ModeSQL Server InstallationDeploy the webpart to SharePoint using PowerShell ScriptReporting Services SharePointReporting Services Add-in for SharePointPowershell ScriptsAdd-SPSolution –LiteralPath "{path on –Identity ReportViewerWebPart.wsp CompatibilityLevel "14,15" -GACDeployment WebApplication {URL to web application}SharePoint SideCreate SSRS Service ApplicationNote: Supports Power BI Report Server reports
SSRS 2017 Now, It is a standaloneapplication as like Power BIReport Server. It can’t be installed on DomainController machine Only one instance is possible ina machine.
Demo Virtual NetworkEnd User SharePoint SSRS ReportEUM Admin PageEUM Landing PageSQL Server Reporting Service Native Mode – Report Viewer web partRLS ReportRLS Database
A Big Thanks to Our Sponsors
Just like Jimi Hendrix We love to get feedbackPlease complete the session feedbackforms
SQLBits - It's all about the community.Please visit Community Corner, we are trying this year to get more peopleto learn about the SQL Community, equally if you would be happy to visitthe community corner we’d really appreciate it.
Q&A
www.dageop.com
EUM & ADFS End User System . All possible solutions in one screen. Authentication Components. FBA Logon Page. Relying Party. Load Balancing NetScaler Domain Controller Firewall. SharePoint Server. Claims Provider. Report Server Reverse Proxy NetScaler Web Application Proxy SQL Database. EUM & ADFS.
