Powershell Security Issues RRS feed

  • Question

  • I'm not sure if this is the right forum for this, but I'm having a problem scripting with powershell.

    I'm trying to bind a SQL connection using Integrated Auth, but when I run the script, I get kicked back this error:

    Exception calling "Fill" with "1" argument(s): "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    If I run this using SQL auth, it works fine.  I don't understand why it's trying to run this using anonymous logon.

    Here is the script:

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet
    $SqlConnection.ConnectionString = "Server=server1;Database=db1;Integrated Security=SSPI;"

    $SqlCmd.CommandText = "insert into Table1 (field1,field2) values ('a','b')
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSetLoc = New-Object System.Data.DataSet

    Tuesday, February 8, 2011 5:29 PM

All replies

  • What account are you logged in with? The most common time I've seen that error from Windows authentication is if you are logged in with an account that is not part of your domain, such as a local computer account, or the domain is having issues resolving your login.

    I was able to successfully run a similar script using Integrated Security=SSPI in my connection string. Can you connect to that instance of the Database Engine and work in that database if you use Windows Authentication in SQL Server Management Studio, or connect using sqlcmd and executing TSQL statements?

    Side note, there is an extra ; at the end of your connection string, but it shouldn't have an effect as my script still ran when I put a ; at the end of my string.

    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, February 9, 2011 12:24 AM
  • I'm logged in using a domain admin account, which also has full admin rights on SQL.  If I open a powershell window and manually enter the liens above, it works just fine.  But if I run it as a script (i.e. powershell.exe -command ...) it fails, just as it does if I run it as a scheduled task using my credentials.
    Thursday, February 10, 2011 12:31 AM
  • Hey Dan,

    This is opening an old as heck conversation but I'm hoping maybe you remember what answer you came up with.

    I have a similar situation where I'm doing an open (vs fill) and I'm getting the logon failed for anonymous. It's only happening against some of my servers and it didn't happen before we moved the script and servers to a new datacenter. As a result I'm thinking the new security settings may be related but it's not happening against all the servers, only a select few. It is however consistent as to which ones and I'm seeing the same issue where running the script manually as the service account from a command prompt works fine but running in a scheduled task has the issue.

    Here's a pared down version of the code

    $integratedSecurity = "True"
    $connString = "Data Source=$serverName;Initial Catalog=$initialCatalog;Integrated Security=$integratedSecurity"
    $sqlConnection = new-object System.Data.SqlClient.SQLConnection $connString
    try {

    Monday, March 9, 2020 7:15 PM