sql-server - Invoke-SqlServerQuery 使用错误的域和用户名
问题描述
出于某种原因,InvokeQuery 模块中的 Invoke-SqlServerQuery 使用错误的用户名来实现集成安全性。例子:
PS C:\> whoami
desktop-7htqmm0\horizon-vm
PS C:\> $env:USERNAME
HORIZON-VM
PS C:\> [System.Security.Principal.WindowsIdentity]::GetCurrent().Name
DESKTOP-7HTQMM0\HORIZON-VM
PS C:\> Invoke-SqlServerQuery -Sql "SELECT * FROM table1" -ConnectionString "server=DESKTOP-7HTQMM0\SQLEXPRESS;database=test1;integrated security=sspi;"
Invoke-SqlServerQuery : Cannot open database "test1" requested by the login. The login failed.
Login failed for user 'MicrosoftAccount\<redacted_email>@gmail.com'.
At line:1 char:1
+ Invoke-SqlServerQuery -Sql "SELECT * FROM table1" -ConnectionString " ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Invoke-SqlServerQuery], SqlException
+ FullyQualifiedErrorId : System.Data.SqlClient.SqlException,InvokeQuery.InvokeSqlServerQuery
但是,SqlPs 模块功能正常:
PS C:\> Invoke-Sqlcmd -ServerInstance DESKTOP-7HTQMM0\SQLEXPRESS -Database test -Query "SELECT * FROM table1"
pk someint somestring somedatetime
-- ------- ---------- ------------
7a836fe1-dda6-4496-8af8-065cf45e4922 213 hey hey hey 9/10/2018 9:09:50 PM
7cb4a34d-74be-4c4b-9ffe-35a076d1c018 321 yo yo yo 9/10/2018 9:09:50 PM
92bae90e-8101-428e-bea3-69c520f6c1d8 123 blah blah blah 9/10/2018 9:09:50 PM
创建数据库后,通过此查询生成表(来自InvokeQuery 的 Github):
use [test]
GO
CREATE TABLE [dbo].[table1](
[pk] [uniqueidentifier] NOT NULL,
[someint] [bigint] NOT NULL,
[somestring] [nvarchar](500) NOT NULL,
[somedatetime] [datetime2](7) NOT NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([pk] ASC)
)
GO
--Seed data
insert into table1 values (NEWID(), 123, 'blah blah blah', GETDATE())
insert into table1 values (NEWID(), 321, 'yo yo yo', GETDATE())
insert into table1 values (NEWID(), 213, 'hey hey hey', GETDATE())
除非为此目的创建 SQL 用户的解决方法,否则我希望集成安全性正常工作。如何让它为当前用户使用正确的名称?
解决方案
好的...很确定我以前遇到过这个确切的问题。看看这篇文章
'invoke-sql' cmdlet 很愚蠢。
推荐阅读
- oracle - 将 2 个 Oracle 查询合并为一个,其中包含一些用 null 指定的通用数据
- for-loop - C code to covert 16bit (RBG565) to grayscale image
- kubernetes - redirect request when authentication failed in Kubernetes ingress controller
- laravel - 删除 Laravel 中的重复代码 - 优化
- python - Access to one specific object via multiple threads
- nginx - Nginx - how to redirect to a new url with or without trailing slash
- r - 从matrix1中获取元素位置,以便删除matrix2中具有确定位置的每个元素
- jmeter - How to get https value from response headers in jmeter
- c# - 按自定义索引和相同性对 C# 中的列表进行排序
- nuxt.js - nuxt.js static site makes api calls at client routing