Mixing
SQL Server EraseSystemHealthPhantomRecords() powershell method connecting to wrong instance of SQL Server
I have a SQL Server named instance that runs the standard syspolicy_purge_history job every night. The job is marked as succeeding every night, but every night on the DEFAULT instance on the same server, there is a failed login connection from the service account of the named instance.
I've tracked it down to the Erase Phantom System Health Records step of the syspolicy_purge_history job. The job uses the standard code and does not appear to have been modified in any way:
if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = 'DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:SQLPolicy$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()
There are two other named instances on the same server, running the same job with the same code, and they do not generate failed login errors on the default instance.
I tried deleting the job, and recreating it by scripting the job from one of the other named instances. It made no difference.
I tried changing the code of step 3 to:
(Get-Item SQLSERVER:SQLPolicy[MyServerName][NamedInstanceName]).EraseSystemHealthPhantomRecords()
and alternatively to:
(Get-Item SQLSERVER:SQLPolicy[MyServerName][NamedInstanceName]`,[NamedInstancePort]).EraseSystemHealthPhantomRecords()
Both versions still generate the failed login on the default instance.
Both versions of the command appear to work successfully if I run them from a powershell window, and do NOT generate a failed login.
I cannot think of anything else to try. Google seems to be out of suggestions for me too.
Any ideas?
powershell sql-server-2012
asked Nov 21 '18 at 15:54
paulHpaulH
797930
add a comment |
I have a SQL Server named instance that runs the standard syspolicy_purge_history job every night. The job is marked as succeeding every night, but every night on the DEFAULT instance on the same server, there is a failed login connection from the service account of the named instance.
I've tracked it down to the Erase Phantom System Health Records step of the syspolicy_purge_history job. The job uses the standard code and does not appear to have been modified in any way:
if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = 'DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:SQLPolicy$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()
There are two other named instances on the same server, running the same job with the same code, and they do not generate failed login errors on the default instance.
I tried deleting the job, and recreating it by scripting the job from one of the other named instances. It made no difference.
I tried changing the code of step 3 to:
(Get-Item SQLSERVER:SQLPolicy[MyServerName][NamedInstanceName]).EraseSystemHealthPhantomRecords()
and alternatively to:
(Get-Item SQLSERVER:SQLPolicy[MyServerName][NamedInstanceName]`,[NamedInstancePort]).EraseSystemHealthPhantomRecords()
Both versions still generate the failed login on the default instance.
Both versions of the command appear to work successfully if I run them from a powershell window, and do NOT generate a failed login.
I cannot think of anything else to try. Google seems to be out of suggestions for me too.
Any ideas?
powershell sql-server-2012
asked Nov 21 '18 at 15:54
paulHpaulH
797930
add a comment |
I have a SQL Server named instance that runs the standard syspolicy_purge_history job every night. The job is marked as succeeding every night, but every night on the DEFAULT instance on the same server, there is a failed login connection from the service account of the named instance.
I've tracked it down to the Erase Phantom System Health Records step of the syspolicy_purge_history job. The job uses the standard code and does not appear to have been modified in any way:
if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = 'DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:SQLPolicy$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()
There are two other named instances on the same server, running the same job with the same code, and they do not generate failed login errors on the default instance.
I tried deleting the job, and recreating it by scripting the job from one of the other named instances. It made no difference.
I tried changing the code of step 3 to:
(Get-Item SQLSERVER:SQLPolicy[MyServerName][NamedInstanceName]).EraseSystemHealthPhantomRecords()
and alternatively to:
(Get-Item SQLSERVER:SQLPolicy[MyServerName][NamedInstanceName]`,[NamedInstancePort]).EraseSystemHealthPhantomRecords()
Both versions still generate the failed login on the default instance.
Both versions of the command appear to work successfully if I run them from a powershell window, and do NOT generate a failed login.
I cannot think of anything else to try. Google seems to be out of suggestions for me too.
Any ideas?
powershell sql-server-2012
asked Nov 21 '18 at 15:54
paulHpaulH
797930
I have a SQL Server named instance that runs the standard syspolicy_purge_history job every night. The job is marked as succeeding every night, but every night on the DEFAULT instance on the same server, there is a failed login connection from the service account of the named instance.
I've tracked it down to the Erase Phantom System Health Records step of the syspolicy_purge_history job. The job uses the standard code and does not appear to have been modified in any way:
if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = 'DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:SQLPolicy$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()
There are two other named instances on the same server, running the same job with the same code, and they do not generate failed login errors on the default instance.
I tried deleting the job, and recreating it by scripting the job from one of the other named instances. It made no difference.
I tried changing the code of step 3 to:
(Get-Item SQLSERVER:SQLPolicy[MyServerName][NamedInstanceName]).EraseSystemHealthPhantomRecords()
and alternatively to:
(Get-Item SQLSERVER:SQLPolicy[MyServerName][NamedInstanceName]`,[NamedInstancePort]).EraseSystemHealthPhantomRecords()
Both versions still generate the failed login on the default instance.
Both versions of the command appear to work successfully if I run them from a powershell window, and do NOT generate a failed login.
I cannot think of anything else to try. Google seems to be out of suggestions for me too.
Any ideas?
powershell sql-server-2012
powershell sql-server-2012
asked Nov 21 '18 at 15:54
paulHpaulH
797930
asked Nov 21 '18 at 15:54
paulHpaulH
797930
asked Nov 21 '18 at 15:54
paulHpaulH
797930
asked Nov 21 '18 at 15:54
paulHpaulH
797930
asked Nov 21 '18 at 15:54
paulHpaulH
797930
797930
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415872%2fsql-server-erasesystemhealthphantomrecords-powershell-method-connecting-to-wro%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415872%2fsql-server-erasesystemhealthphantomrecords-powershell-method-connecting-to-wro%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
