header1   header
header
header Register : : Login header
header
connector   connector
menuleft menuright
submenu   submenu
left
using sql server authenication with SMO
Last Post 29 Nov 2009 04:01 PM by Chad Miller. 3 Replies.
Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
chartUser is Offline
New Member
New Member
Posts:25
Avatar

--
27 Nov 2009 01:05 PM
    I have a powershell script that uses SMO to get database info from several servers that are in a table.  I need to be able to login to a few servers using sql server authenication (instead of windows authenication).  I cannot get this to work no matter what I try.  My lastest attempt is attached.   Please help someone new to powershell.  Thanks


    try983.ps1

    Chad MillerUser is Offline
    Basic Member
    Basic Member
    Posts:160
    Avatar

    --
    27 Nov 2009 05:45 PM

    There are couple of ways to connect to a SQL Server using SMO with SQL authentication:

    Method 1, create and pass a SQL Connection object to the SMO.Server constructor:

    $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") "MyServer\Myinstance","sa","mypassword"
    $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $con

    Method 2, first create an SMO.Server object and then set

    $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "MyServer\Myinstance"
    $server .ConnectionContext.LoginSecure = $false
    $server .ConnectionContext.Login = "sa"
    $server .ConnectionContext.Password = "mypassword"

    I noticed you are using the SQL 2008 invoke-sqlcmd cmdlet, SQL authenication is also supported see get-help invoke-sqlcmd and use the -username and -password parameters.



    chartUser is Offline
    New Member
    New Member
    Posts:25
    Avatar

    --
    29 Nov 2009 03:44 PM
    Thanks for the input. I used option two and it worked great.
    This is probalbly not the correct place for this question. What is the least you can give a sql server user so that they can use Microsoft.SqlServer.Management.Smo.Server to collect
    $db.size - ($db.logfiles|measure-object -property size -sum).sum / 1024


    Chad MillerUser is Offline
    Basic Member
    Basic Member
    Posts:160
    Avatar

    --
    29 Nov 2009 04:01 PM
    Not sure, SMO uses the underlying security context of login executing the SMO command. Each SMO command ultimately executes a SQL Query. Assuming the logfile meta data is derived from sys.datababase_fiiles, according to Books Online anyone in the public role for a particular database can retrieve file meta data.


    You are not authorized to post a reply.


    Active Forums 4.3
    right
    footer   footer
    footer Sponsored by Quest Software • SAPIEN Technologies • Compellent • Microsoft Windows Server 2008 R2 footer
    footer   footer