by Contributed | Jun 28, 2024 | Technology
This article is contributed. See the original author and article here.
In this guide I am going to show steps to connect Windows Azure VM to Azure SQL DB using Managed Identity covering create user in Azure SQL DB , connect using SSMS and connect using powershell
Requirements:
Windows 10 or 11 Azure Virtual Machine with system managed identity enabled and admin privileges to run powershell scripts
Azure SQL DB server with entra admin access and database for testing
SQL Server Management Studio (SSMS) latest version
Get required information from VM and managed identity:
Use Object (principal) ID to get Application ID
Go to Entra ID and search Object (principal) ID
Select result to get Application ID
Provide access to Azure SQL DB:
Connect to server/database using Entra user with admin privileges and create user in this case is the name of the computer
— DROP USER [managediddemo] –remove user if exists
CREATE USER [managediddemo] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [managediddemo];
Connect from Azure VM:
Connect using SQL Server Management Studio SSMS …
Open SSMS and provide server name , select authentication Microsoft Entra Managed Identity and user assigned Identity will be Application ID from VM
In connection properties provide database name otherwise you will receive an error if user is not administrator and finally connect
Now is connected
Connect using powershell…
To be able to connect using powershell you need to Install modules required for Azure
Open powershell as administrator and run commands below
Set-ExecutionPolicy unrestricted
Install-Module -Name PowerShellGet -Force
Install-Module -Name Az -AllowClobber -Scope CurrentUser -force
Install-module SQLServer -force
Once modules are installed you can close powershell and open again as administrator
Get token
Connect-AzAccount -Identity
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
write-host $access_token
*In some scenarios token string can be provided directly to avoid round trip each time
Test with invoke-sqlcmd
Connect-AzAccount -Identity
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
Invoke-Sqlcmd -ServerInstance .database.windows.net -Database -AccessToken $access_token -query ‘select top 10 name from sys.tables’
-query is the query to run in this case only gets a list of tables in database
Test using Microsoft.Data.SQLClient
import-module Az.Accounts
import-module Microsoft.PowerShell.Security
import-module Microsoft.WSMan.Management
import-module SqlServer
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$connectionstring=”Server=tcp:.database.windows.net,1433; Database=; Encrypt=True;”
$connection= New-Object Microsoft.Data.SqlClient.SqlConnection
# you can get connection string from azure portal in database overview
$connection.ConnectionString = $connectionstring
$connection.AccessToken=$access_token
$connection.Open()
$command= $connection.CreateCommand()
$command.CommandText = “select top 10 name from sys.tables”
$dataSet = New-Object system.Data.DataSet
$adapter = New-Object microsoft.Data.SqlClient.SqlDataAdapter $command
$adapter.Fill($dataSet) | Out-Null
$connectionid=$connection.clientconnectionid
write-output $connectionid
$dataSet.Tables
Now your Windows Azure VM is able to connect using different methods
More Information
Provision Azure AD admin (SQL Database)
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell#provision-azure-ad-admin-sql-database
What are managed identities for Azure resources?
https://learn.microsoft.com/en-us/entra/identity/managed-identities-azure-resources/overview
Configure managed identities on Azure virtual machines (VMs)
https://learn.microsoft.com/en-us/entra/identity/managed-identities-azure-resources/how-to-configure-managed-identities?pivots=qs-configure-portal-windows-vm
by Contributed | Jun 27, 2024 | Technology
This article is contributed. See the original author and article here.
We’re thrilled to announce the public preview of the Microsoft Entra PowerShell module, a new high-quality and scenario-focused PowerShell module designed to streamline management and automation for the Microsoft Entra product family. In 2021, we announced that all our future PowerShell investments would be in the Microsoft Graph PowerShell SDK. Today, we’re launching the next major step on this journey. The Microsoft Entra PowerShell module (Microsoft.Graph.Entra) is a part of our ongoing commitment and increased investment in Microsoft Graph PowerShell SDK to improve your experience and empower automation with Microsoft Entra.
We’re grateful for the substantial feedback we’ve heard from Microsoft Entra customers about our PowerShell experiences, and we’re excited to hear your thoughts after evaluating this preview module. We plan to build on our investment in the Microsoft Entra PowerShell module going forward and expand its coverage of resources and scenarios.
What is Microsoft Entra PowerShell?
The Microsoft Entra PowerShell module is a command-line tool that allows administrators to manage and automate Microsoft Entra resources programmatically. This includes efficiently managing users, groups, applications, service principals, policies, and more. The module builds upon and is part of the Microsoft Graph PowerShell SDK. It’s fully interoperable with all cmdlets in the Microsoft Graph PowerShell SDK, enabling you to perform complex operations with simple, well-documented commands. The module also offers a backward compatibility option with the deprecated AzureAD module to accelerate migration. Microsoft Entra PowerShell supports PowerShell version 5.1 and version 7+. We recommend using PowerShell version 7 or higher with the Microsoft Entra PowerShell module on all platforms, including Windows, Linux, and macOS.
Benefits of Microsoft Entra PowerShell
- Focus on usability and quality: Microsoft Entra PowerShell offers human-readable parameters, deliberate parameter set specification, inline documentation, and core PowerShell fundamentals like pipelining.
- Backward compatibility with AzureAD module: Microsoft Entra PowerShell accelerates migration from the recently announced AzureAD module deprecation.
- Flexible and granular authorization: Consistent with Microsoft Graph PowerShell SDK, Microsoft Entra PowerShell enables administrative consent for the permissions you want to grant to the application and supports specifying your own application identity for maximum granularity in app permission assignment. You can also use certificate, Service Principal, or Managed Identity authentication patterns.
- Open source: The Microsoft Entra PowerShell module is open source, allowing contributions from the community to create great PowerShell experiences and share them with everyone. Open source promotes collaboration and facilitates the development of innovative business solutions. You can view Microsoft’s customizations and adapt them to meet your needs.
Next steps
Installation: Install Microsoft Entra PowerShell, which uses the “/v1.0” API version to manage Microsoft Graph resources, from the PowerShell Gallery by running this command:
Install-Module Microsoft.Graph.Entra -AllowPrerelease -Repository PSGallery -Force
|
Or install the Beta module, which manages Microsoft Graph resources using the “/beta” API version, by running this command:
Install-Module Microsoft.Graph.Entra.Beta -AllowPrerelease -Repository PSGallery -Force |
Authentication: Use the Connect-Entra command to sign in to Microsoft Entra ID with delegated access (interactive) or application-only access (noninteractive).
Connect-Entra -TenantId ‘your-tenant-id’ -Scopes ‘User.Read.All’
|
To see more examples for using your own registered application, Service Principal, Managed Identity, and other authentication methods, see the Connect-Entra command documentation.
Find all available commands: You can list all available commands in the Microsoft Entra PowerShell module by using the command:
Get-Command –Module Microsoft.Graph.Entra
|
Get Help: The Get-Help command shows detailed information about specific commands, such as syntax, parameters, cmdlet description, and usage examples. For example, to learn more about the Get-EntraUser command, run:
Get-Help Get-EntraUser –Full
|
Migrating from AzureAD PowerShell module: You can run your existing AzureAD PowerShell scripts with minimal modifications using Microsoft Entra PowerShell by using the Enable-EntraAzureADAlias command. For example:
Import-Module -Name Microsoft.Graph.Entra
Connect-Entra #Replaces Connect-AzureAD for auth
Enable-EntraAzureADAlias #enable aliasing
Get-AzureADUser -Top 1
|
Frequently Asked Questions (FAQs)
What is the difference between the Microsoft Graph PowerShell SDK and Microsoft Entra PowerShell modules?
Microsoft Entra PowerShell is a part of our increased investment in Microsoft Graph PowerShell SDK. It brings high-quality and scenario-optimized Entra resource management to the Microsoft Graph PowerShell SDK. Still, it keeps all the benefits of Microsoft Graph PowerShell SDK for authorization, connection management, error handling, and (low-level) API coverage. As Microsoft Entra PowerShell builds on the Microsoft Graph PowerShell SDK, it is completely interoperable.
Is the Microsoft Entra PowerShell module compatible with Microsoft Graph PowerShell?
Yes. You don’t need to switch if you’ve already used the Microsoft Graph PowerShell module. Both modules work well together, and whether you use Entra module cmdlets or Microsoft Graph PowerShell SDK cmdlets for Entra resources is a matter of preference.
I need to migrate from the deprecated AzureAD or MSOnline modules. Should I wait for Microsoft Entra PowerShell?
No. One of our goals with Microsoft Entra PowerShell is to help you migrate from Azure AD PowerShell more quickly by setting Enable-EntraAzureADAlias. Microsoft Entra PowerShell supports simplified migration for scripts that were using AzureAD PowerShell, with over 98% compatibility. However, the legacy AzureAD and MSOnline PowerShell modules are deprecated and will be retired (stop working) after March 30, 2025. We recommend that you act now to begin migrating your MSOnline and AzureAD PowerShell scripts.
Both modules use the latest Microsoft Graph APIs. For test environments and non-production systems, you can migrate to Microsoft Entra PowerShell. We recommend migrating to this module for production systems only after it reaches general availability. If you migrate scripts to Microsoft Graph PowerShell SDK now, there is no need to update them again with Microsoft Entra PowerShell, as it enhances and will not replace Microsoft Graph PowerShell SDK.
Should I update Microsoft Graph PowerShell scripts to Microsoft Entra PowerShell?
This is not necessary but a matter of preference. Microsoft Entra PowerShell is part of the Microsoft Graph PowerShell solution, and the two modules are interoperable. You can install both modules side-by-side.
Will Microsoft Entra PowerShell add support for more resources in the future?
Yes, it is a long-term investment. We will continue to expand support for more resources and scenarios over time. Expect new cmdlets for Privileged Identity Management (PIM), Entitlement Management, Tenant Configuration settings, Per-User multifactor authentication (MFA), and more. We’ll also enhance existing cmdlets with additional parameters, detailed help, and intuitive names. Check out GitHub repo for ongoing updates.
Will Microsoft Entra PowerShell use a pre-consented app like AzureAD or MSOnline modules?
No. Microsoft Entra PowerShell permissions aren’t preauthorized, and users must request the specific app permissions needed. This granularity ensures that the application has only the necessary permissions, providing granular control over resource management. For maximum flexibility and granularity in application permissions, we recommend using your own application identity with Entra PowerShell. By creating different applications for different uses of PowerShell in your tenant, you can have exacting control over application permissions granted for specific scenarios. To use your own application identity with Microsoft Entra PowerShell, you can use the Connect-Entra cmdlet:
Connect-Entra -ClientId ‘YOUR_APP_ID’ -TenantId ‘YOUR_TENANT_ID’
|
I am new to Microsoft Entra PowerShell; where do I start?
Explore our public documentation to learn how to install the Microsoft Entra PowerShell module, authenticate, discover which cmdlet to use for a particular scenario, read how-to guides, and more. Our best practice guide will help you start on a secure foundation.
How can I provide feedback?
You can provide feedback by visiting our GitHub repository issues section. Create a new issue with your feedback, suggestions, or any problems you’ve encountered. Our team actively monitors and responds to feedback to improve the module.
How can I contribute?
We welcome contributions from the community, whether it’s through submitting bug reports, suggesting new features, or contributing scenario and example improvements. To get started, visit the GitHub repository, check out our contribution guidelines, and create a pull request with your changes.
Learn more about Microsoft Entra PowerShell module
Explore our public documentation, to learn how to install the Microsoft Entra PowerShell module, the authentication methods available, which cmdlet to use for a particular scenario, how-to guides, and more.
Try It Today
Try out the new version and let us know what you think on GitHub! Your insights are invaluable as we continue to improve and enhance the module to better meet your needs.
Thank you!
We want to thank all the community members who helped us improve this release by reporting issues on GitHub during the private preview! Please keep them coming!
Steve Mutungi
Product Manager, Microsoft Entra PowerShell
Read more on this topic
Learn more about Microsoft Entra
Prevent identity attacks, ensure least privilege access, unify access controls, and improve the experience for users with comprehensive identity and network access solutions across on-premises and clouds.
by Contributed | Jun 26, 2024 | Technology
This article is contributed. See the original author and article here.
We’re excited to announce that the migration tool for Active Directory Federation Service (AD FS) customers to move their apps to Microsoft Entra ID is now generally available! Customers can begin updating their identity management with more extensive monitoring and security infrastructure by quickly identifying which applications are capable of being migrated and assessing all their AD FS applications for compatibility.
In November we announced AD FS Application Migration would be moving to public preview, and the response from our partners and customers has been overwhelmingly positive. For some, transitioning to cloud-based security is a daunting task, but the tool has proven to dramatically streamline the process of moving to Microsoft Entra ID.
A simplified workflow, reduced need for manual intervention, and minimized downtime (for applications and end users) have reduced stress for hassle-free migrations. The tool not only checks the compatibility of your applications with Entra ID, but it can also suggest how to resolve any issues. It then monitors the migration progress and reflects the latest changes in your applications. Watch the demo to see the tool in action.
Moving from AD FS to a more agile and responsive, cloud-native solution helps overcome some of the inherent limitations of the old way of managing identities.
In addition to more robust security, organizations count greater visibility and control with a centralized, intuitive admin center and reduced server costs as transformative benefits of moving to a modern identity management. Moreover, Entra ID features can help organizations achieve better security and compliance with multifactor authentication (MFA) and conditional access policies—both of which provide a critical foundation for Zero Trust strategy.
More Entra ID features include:
Want to learn more about Microsoft Entra? Get the datasheet and take a tour here. Ready to get started? Visit Microsoft Learn and explore our detailed AD FS Application Migration guide.
Have any questions or feedback? Let us know here.
Melanie Maynes
Director of Product Marketing
For a comprehensive overview of the migration tool and its capabilities, check out these other resources:
Learn more about Microsoft Entra
Prevent identity attacks, ensure least privilege access, unify access controls, and improve the experience for users with comprehensive identity and network access solutions across on-premises and clouds.
by Contributed | Jun 25, 2024 | Technology
This article is contributed. See the original author and article here.
Recently, I received a question about unattended uninstall for SQL Server Express edition. This article describes how to perform this task.
We need to take in consideration before to proceed
- User that performs the process must be a local administrator with permissions to log on as a service. You can review more information about required permissions here.
- If the machine has the minimum required amount of physical memory, increase the size of the page file to two times the amount of physical memory. Insufficient virtual memory can result in an incomplete removal of SQL Server.
- On a system with multiple instances of SQL Server, the SQL Server browser service is uninstalled only once the last instance of SQL Server is removed. The SQL Server Browser service can be removed manually from Programs and Features in the Control Panel.
- Uninstalling SQL Server deletes
tempdb data files that were added during the install process. Files with tempdb_mssql_*.ndf name pattern are deleted if they exist in the system database directory.
- Backup your databases.
Uninstall process
- Open Command Prompt with admin rights
- Navigate to the below directory
C:Program FilesMicrosoft SQL Server160Setup BootstrapMyInstanceName
Please note that 160 version is the major build version of SQL Server.
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/view-and-read-sql-server-setup-log-files?view=sql-server-ver15
C:>cd C:Program FilesMicrosoft SQL Server160Setup BootstrapSQL2022
- Run the below command
C:Program FilesMicrosoft SQL Server160Setup BootstrapSQL2022>setup.exe /Q /ACTION=uninstall /INSTANCENAME=SQLEXPRESS /FEATURES=SQLENGINE
For more information on uninstall options, see
https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver16#Uninstall
Hope this helps!
Idalia – SQL Server CSS
by Contributed | Jun 24, 2024 | Technology
This article is contributed. See the original author and article here.
In this post, we’ll cover some details on how to track the lifecycle of a SharePoint Site in the Microsoft Graph Data Connect (MGDC), using the date columns in the SharePoint Site dataset. If you’re not familiar with MGDC for SharePoint, start with https://aka.ms/SharePointData.
All Dates in the Sites Dataset
One of the most common scenarios in MGDC for SharePoint is tracking the lifecycle of a site, which includes understanding when the site was created, how it grows over time, when it stops growing and when it becomes inactive or abandoned.
The SharePoint Sites dataset includes several columns that can be used to understand the site lifecycle in general. For instance, here are the datetime columns available:
Site Created
Creation date is straightforward. There is a column (CreatedTime) with the date when the site was created. As with all other dates, it uses the UTC time zone.
Last Modified
In the Sites dataset, you also have the date and time when any items under the root web were last modified (RootWeb.LastItemModifiedDate). This includes the last time when files were created, updated or deleted. This is a great indication that the Site is still in active use.
You also have the date the site security was last modified (LastSecurityModifiedDate). This shows when permissions were granted, updated or revoked. That includes permissions granted through the manage access interface and permissions granted through sharing links.
Last Accessed
Last access is available at the site level (LastUserAccessDate). This shows when an item in the site was last accessed (this includes simply reading the file). This is an important indicator to help understand when the site is becoming inactive or abandoned.
Note that, while there is an effort to identify here only access performed directly by users, this date might also include automated actions by applications, including internal SharePoint applications.
Snapshot Date
Please note that there is one more date (SnapshotDate), but that one is not relevant to the site lifecycle. The snapshot date simple tracks when the data was retrieved by MGDC.
File Actions
Besides what’s captured in these datetime columns in the Sites dataset, you also have the option to capture detailed file activity in the site using the SharePoint File Actions and accumulate those over time.
Keep in mind that MGDC for SharePoint only keeps actions for the last 21 days due to compliance issues. More specifically, you can get file actions between today minus 2 days and today minus 23 days. For instance, if today is June 30th, you can get file actions between June 8th and June 28th.
If you query this information daily, you could build a longer history of file actions over time. For instance, you could keep the last 90 days of data from the File Actions dataset. With that you could find recent access or otherwise say “no access in the last 90 days”.
This would also let you know more details about recent file activities, like who last accessed the site, which file or extension was last accessed, what was the last action, etc. You need to decide if you can rely solely on the date columns provided in the Sites dataset or if it is useful to keep these additional details.
Please do check with the compliance team in your company to make sure there are no restrictions on keeping this information for longer periods of time in your country. There might be regulatory restrictions on how long you can keep this type of personally identifiable information.
Calculated Columns
Keep in mind that these dates use a datetime data type, so grouping by one of them can sometimes be a challenge. If you’re using Power BI, you can show them as a date hierarchy and get a summary by year, quarter, month or day.
It might also be useful to create calculated columns to help with grouping and visualization. For instance, you can create a new date column (without the time portion) for daily summaries. Here’s how to calculate that in Power BI:
CreatedDay = DATE(YEAR(Sites[CreatedTime]),MONTH(Sites[CreatedTime]), DAY(Sites[CreatedTime]))
You could also create a column for monthly summaries like this:
CreatedMonth = FORMAT(Sites[CreatedTime], "yyyy-MM")
You can read more about calculated columns in Power BI at MGDC for SharePoint FAQ: How to create custom columns in Power BI.
Conclusion
I hope this clarifies what is available in MGDC for SharePoint to track the lifecycle of a SharePoint site.
Let us know in the comments if you think we should consider additional lifecycle information.
For further details about the schema of all SharePoint datasets in MGDC, including SharePoint Sites and SharePoint File Actions, see https://aka.ms/SharePointDatasets.
Recent Comments