By Chi Wai Chan, Product Development Solution Architect at Alibaba Cloud
In the previous article , we introduced ApsaraDB for MyBase, which is a dedicated service for managing databases on the cloud provided by Alibaba Cloud. We also talked about how to do Microsoft SQL Server Cloud Migration, not just for the database engine, but also with the SSIS which is the data integration pipeline (ETL workflow).
There are two parts for Migrating your SQL Server and SSIS workload with Active Directory (AD) integration to Alibaba Cloud. If you have not read Part 1, you can do so by clicking on this link.
This article is a continuation of Part 1, and focuses on integration with Active Directory (AD) for domain login.
Microsoft Active Directory (AD) is a directory service for Windows Standard Server, Windows Enterprise Server, and Microsoft SQL Server provided by Microsoft. A directory is a hierarchical structure used to store information about objects on the same local area network. For example, AD stores information about a user's account, such as name, password, phone number, and so on, and allows other authorized users on the same local area network to access this information.
AD is an important component of the Windows ecosystem. Many large enterprises will realize integrated centralized access management through domain control, which is the original management mode that enterprises rely on for a long time. In this context, when you migrate a holistic service from a homegrown environment to the cloud or use a hybrid cloud architecture, you often also need to support AD services in the cloud architecture to facilitate global management. Specific to SQL Server database, you need the AD authentication for some database administration works, such as remotely deploy SSIS package.
For example, you have hit the following error if you want to deploy SSIS on visual studio remotely with SQL Server authentication. Instead you have to use windows authentication to make it work.
As a result, you can use windows authentication for cross machines login to SQL Server and have remote deployment of your SSIS package via visual studio SQL Server Data Tools (SSDT) with following this best practice guide.
There are four major steps for setup the AD integration for windows authentication on SQL Server, which are:
Setup Active Directory Domain Service (AD DS) on a ECS instance which located in the same VPC of the ApsaraDB MyBase RDS for SQL Server instance deployed in the previous article. You can also connect your on-premises AD DS deployment to the same VPC where the ApsaraDB MyBase RDS for SQL Server instance is deployed. But this article focus on creating a dedicated AD DS on ECS.
Please follow this guide on setting up an ECS instance with Microsoft Server 2016, which is located in the same VPC of the ApsaraDB MyBase RDS for SQL Server instance deployed in the previous article.
Please follow this guide on setting up AD DS on the ECS that you have previously provisioned. You can set the root domain name and netbios domain name according to your naming standard, and here we would like to use "mybase.local" and "MYBASE" respectively in this demo. And then create some users (e.g. "mssqlm1", "mssqlm2, "tester") for the AD DS according to this guide: Add New User.
Get the IP address for the AD Domain Server from the ECS home page (e.g. "192.168.100.5" in this demo).
Each ECS instance must belong to at least one security group. If no security groups have been created when you create an ECS instance, a default security group is created. The default security group has only inbound rules configured for the ICMP protocol, SSH port 22, RDP port 3389, HTTP port 80, and HTTPS port 443. For more information, see Overview. If you do not want the ECS instance to be added to the default security group, you can create a security group as described in this topic.
To enable the ECS on providing AD DS service, you have to open the corresponding ports with setup up the security group as follow diagram. For detail on setup security group, you can refer to this guide: Create a security group for ECS.
Add the ECS instance for AD DS to the security group previously created by following this guide on Adding Security Group to an ECS instance.
Please also turn off your windows firewall by following this guide: turn off windows firewall on Windows 2016.
In a Domain Name System (DNS), a DNS forwarder is a DNS server that is used to forward DNS queries for external DNS names to DNS servers outside that network. It does this for DNS queries that it cannot be resolved locally, meaning DNS queries that it has no personal knowledge of. By using DNS forwarders you can improve the efficiency of name resolution for the computers in your network that query for DNS names outside your network (such as names on the Internet).
In the ApsaraDB MyBase instances, except for resolving the domain group name, you should leverage the features provided on DNS forwarder to forward all queries for external DNS names to DNS servers outside that network (e.g. the default DNS servers). And then, you can set up DNS conditional forwarder to forward domain group name (mybase.local) to our dedicated AD DS.
Now, you have to login the windows host that is running the primary SQL Server instances. You have to login to two windows hosts via bastion host. For a detailed step-by-step guide on bastion host login, please refer to the previous article.
Setup DNS server on the two windows hosts for MyBase according to this guide: DNS Server setup.
Obtain the DNS server information by running "ipconfig /all" in command windows as shown in the below diagram. And you would find the current DNS Servers values are "100.100.2.136" and "100.100.2.138".
Configure the DNS forwarder according to this guide: DNS Forwarder Configuration. And put the DNS Servers ip addresses (e.g. "100.100.2.136" and "100.100.2.138") acquired in previous step into the tab "IP Address" in DNS forwarder.
Configure the DNS conditional forwarder according to this guide: DNS Conditional Forwarder Configuration. Please set the DNS domain name as the one you created in Step 1 (e.g. mybase.local), and the ECS private IP (e.g. 192.168.100.5) as shown according to step 1.3;
Configure network settings for using local DNS server according to this guide: Configure windows network settings. And set the network configuration to use local as DNS server as follow diagram.
Add your PC into the domain group "MYBASE". To join a computer to a domain please follow the steps below:
Last, please login to the window host which running the secondary SQL server instance, and then repeat previous steps (3.1 to 3.6).
Now you can login the SQL server primary instance with SQL login, and create user with windows authentication for remote logins (e.g. "MYBASEmssqlm1", "MYBASEmssqlm2", "MYBASEtester"), and grant your required permission to those users.
You would have with deploying SSIS-packages to our SSISDB catalog on SQL Server, the error message we started having was:
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'microsoft.sqlserver.integrationservices.server, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)The error could be reproduced by starting SQL Server Management Studio (SSMS) and trying to create a new folder inside the SSISDB-catalog. You have to resolve this remote trust login issue by login the primary SQL Server instance via the SQL Server Management Studio with sysadmin login and running the following command:
use master;
Create Login ##MS_SQLEnableSystemAssemblyLoadingUser## FROM Asymmetric Key MS_SQLEnableSystemAssemblyLoadingKey ;
Grant Unsafe Assembly to ##MS_SQLEnableSystemAssemblyLoadingUser## ;Now, you should configure your workstation, which should have SSMS and SSDT installed, to join the AD DS as shown in step 3.6. Then log out your workstation, and log in again with the AD DS user (e.g. "MYBASEtester"), and now you should able to login the SSMS and deploy the SSDT with windows authentication as shown in the below diagram.
Now, you should be able to build and then remotely deploy SSIS package to SQL Server.
And finally, log in to SSMS with admin right, select "integration Service Catalogs, and right click on the deployed package, and click on configure. Change the server Name to ".,3001" which 3001 is the database instance number, you can find in the previous article. Also update the password, and change the ConnectionString by replacing the host address to also ".,300". Click validate, and you would see the package is being validated successfully.
Congratulations! You have now successfully migrated your SQL Server and SSIS to ApsaraDB MyBase on Alibaba Cloud.
ApsaraDB for Redis 6.2: Enhanced Geographic Location Features
ApsaraDB - March 4, 2021
ClouderLouder - November 25, 2020
Alibaba Clouder - September 6, 2019
Rupal_Click2Cloud - July 27, 2021
Alibaba Clouder - February 28, 2019
Alibaba Cloud Community - September 27, 2021
Cloud Migration Solution
Secure and easy solutions for moving you workloads to the cloud
Learn More
Database Migration Solution
Migrating to fully managed cloud databases brings a host of benefits including scalability, reliability, and cost efficiency.
Learn More
Oracle Database Migration Solution
Migrate your legacy Oracle databases to Alibaba Cloud to save on long-term costs and take advantage of improved scalability, reliability, robust security, high performance, and cloud-native features.
Learn More
ADAM(Advanced Database & Application Migration)
An easy transformation for heterogeneous database.
Learn MoreMore Posts by ApsaraDB
