英文:
SQL Server Management Studio can connect but Powershell cannot
问题
我是一个数据库管理员，我正在尝试编写一个可以读取包含 SQL Server 实例名称的文本文件、通过 POWERSHELL 连接到这些服务器、从这些服务器中获取信息（最重要的是它们的 SQL Server 版本号）并将这些信息导出到另一个文本文件的脚本。
就我所知，我已经做得差不多了，但我目前遇到的问题是在某些服务器上，POWERSHELL 无法获取这些信息，但我可以使用 SSMS 正常连接到这些服务器。
执行大部分重要工作的代码是：
```$sql_svr = new-object "microsoft.sqlserver.management.smo.server" $machine_name #$sql_instance;```
当我将鼠标悬停在 $sql_svr 上时，在成功连接时我会得到以下结果：
[![在这里输入图片描述][1]][1]
但如果 POWERSHELL 无法连接，悬停时将不显示任何内容。
在那些无法连接的服务器上，必须采取哪些措施以使 POWERSHELL 能够获得成功的结果？
英文:
I'm a DBA and I'm trying to come up with a POWERSHELL script that can read a text file filled with SQL Server instance names, connect to those servers via POWERSHELL, poll said servers for information (most importantly their sql server version number) and export this information into another text file.
For the most part what I have come up with works fine as is, but the problem I'm currently running into is on some servers POWERSHELL cannot poll this information but yet I can connect up to those servers fine with SSMS.
The code that does most of the heavy lifting is:
$sql_svr = new-object "microsoft.sqlserver.management.smo.server" $machine_name #$sql_instance;
This code, when I hover over $sql_svr I get these results on a successful connection:
But if POWERSHELL cannot connect, the hover shows nothing.
What has to happen on the servers not connecting to allow POWERSHELL to get successful results?
答案1
得分: 1
今天在其中一台服务器上，我碰到了这个问题。看起来是与最新的"SqlServer"（>v22.0）模块版本有关。当我将"SqlServer"模块降级到v21.1.18218时，它开始正常工作。
英文:
I happened to face this issue today in one of the server machine. Looks like it was the problem with latest "SqlServer"(>v22.0) module versions. When I downgraded the "SqlServer" module to v21.1.18218 it started to work.
答案2
得分: 0
我与我们的服务器团队合作，确定这是一个需要解决的防火墙端口问题。 一些服务器在构建时由服务器团队打开了它们的端口，而另一些服务器没有。
一旦服务器团队确定了需要打开的端口，一切都开始正常工作了。
可能的端口是445和5985或5986。 我不确定它们是什么，但他们创建了一个组策略规则，一旦运行它们，我运行了GPUPDATE /force，然后运行了我的远程PS脚本，它正常运行了。
英文:
I worked with our Server team to determine it was a firewall port issue that needed addressed. Some servers had their ports open originally by the server team when they were built and some servers did not.
Once the server team determined what ports needed opened up, everything started working.
Potential ports are 445 and 5985 or 5986. I'm not sure what those are, but they created a Group Policy rule and once they ran it, I ran a GPUPDATE /force and then ran my remote PS script and it ran fine.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论