当前位置：文江博客话题详情

使用read-sqltabledata读取azsql表的同时，使用Azure AD帐户进行身份验证

发布于 2025-01-20 15:57:38 字数 1329 浏览 0 评论 0 原文

连接到？

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') "<>.database.windows.net"
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.Authentication = [Microsoft.SqlServer.Management.Common.SqlConnectionInfo+AuthenticationMethod]::ActiveDirectoryPassword
$srv.ConnectionContext.Login = "<>@<>.onmicrosoft.com"
$srv.ConnectionContext.Password = "<>"
$srv.ConnectionContext.Connect()

错误： MethodInvocationException：异常调用“连接”使用“ 0”参数

： /lealen.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.common.common.sqlconnectioninfo.authenticationmethod?view = sql-smo-160 。 I'm trying to run

Read-SqlTableData -InputObject $srv.Databases["TestDB"].Tables["TestTable"] -TopN 5

期望使用 read-Sqltabledata 在AZSQL上为Azad 用户 ID上的答案代码 ID。

原文

What other properties does one need to add to the ConnectionContext while connecting to AzSQL using an AzAD account?

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') "<>.database.windows.net"
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.Authentication = [Microsoft.SqlServer.Management.Common.SqlConnectionInfo+AuthenticationMethod]::ActiveDirectoryPassword
$srv.ConnectionContext.Login = "<>@<>.onmicrosoft.com"
$srv.ConnectionContext.Password = "<>"
$srv.ConnectionContext.Connect()

Error :
MethodInvocationException: Exception calling "Connect" with "0" argument(s): "Failed to connect to server <>.database.windows.net."

The ActiveDirectoryPassword method works fine from SSMS. I'm trying to run Read-SqlTableData instead of invoking a TSQL query.

Read-SqlTableData -InputObject $srv.Databases["TestDB"].Tables["TestTable"] -TopN 5

Expecting screenshot or screenclip GIF that the answer code is working for an AzAD userid on AzSQL using Read-SqlTableData.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

懷念過去 2025-01-27 15:57:38

正如亲自讨论的，这只是因为 Read-SqlTableData 尚未更新以支持 -AccessToken 参数。

我已经收到了一些对其他 cmdlet 的请求...所以我会尽力尽快获取它。

回复收藏 0 原文

翻身的咸鱼 2025-01-27 15:57:38

请确保登录为管理员。用户必须具有Azure Ad Admin角色才能连接服务器。

并确保用户从Azure Portal iam 访问控制中具有 diveTory读者角色。

另外，请确保您已为SQL Server分配了AAD管理员。
尝试在Azure AD中创建服务主体（如果尚未存在），然后给予DB_READER或DB_WRITER或授予的DB_owner权限。

CREATE USER [user or msi] FROM EXTERNAL PROVIDER;

ALTER ROLE [db_datareader] ADD MEMBER <Azure_AD_principal_name>  --Grant the user db_datareader permissions on that database
GO

或如果需要（如果

ALTER ROLE [db_datawriter] ADD MEMBER <Azure_AD_principal_name>;

需要）或所有者许可（如果需要

ALTER ROLE [db_owner] ADD MEMBER <Azure_AD_principal_name>

）或 使用服务主体在Azure SQL DB中使用服务主体创建Azure AD用户 -Azure SQL数据库| Microsoft Docs

请检查下面的参考文献：

Please make sure to login as administrator .The user must have azure ad admin role for server to connect.

And make sure the user has diretory readers role from azure portal IAM access control.

Also Ensure that you've assigned an AAD admin for the SQL Server.
Try to create a service principal in azure ad if not already present and then give db_reader or db_writer or db_owner permissions granted .

CREATE USER [user or msi] FROM EXTERNAL PROVIDER;

ALTER ROLE [db_datareader] ADD MEMBER <Azure_AD_principal_name>  --Grant the user db_datareader permissions on that database
GO

or write permission if required

ALTER ROLE [db_datawriter] ADD MEMBER <Azure_AD_principal_name>;

Or owner permission if required

ALTER ROLE [db_owner] ADD MEMBER <Azure_AD_principal_name>

Or Create Azure AD users in azure sql DB using service principals - Azure SQL Database | Microsoft Docs

Please check below references:

回复收藏 0 原文

葬花如无物 2025-01-27 15:57:38

参数已在中添加到cmdlet中模块/sqlserver/？view = sqlserver-ps“ rel =“ nofollow noreferrer”> sqlserver模块。

Read-SqlTableData -AccessToken (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token `
-ServerInstance <>.database.windows.net -SchemaName dbo -DatabaseName <> -TableName <> -TopN 3 |FT

输出

An AccessToken parameter has been added to the cmdlet in version 22.0.59 of the SqlServer Module.

Read-SqlTableData -AccessToken (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token `
-ServerInstance <>.database.windows.net -SchemaName dbo -DatabaseName <> -TableName <> -TopN 3 |FT

Output