Connection to MS SQL with username

Tagged: ,

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Don Jones Don Jones 3 weeks, 3 days ago.

  • Author
    Posts
  • #74227
    Profile photo of Krzysztof
    Krzysztof
    Participant

    Hello experts!
    I want to script my remote MS SQL database to local .sql files. I found correct powershell script which resolved my conception: link to GitHub). I have only one problem – the script works fine only on local machine with local credentials. To database can be connect both options username or local windows credentials.
    As below is part of this script:

    function GenerateDBScript([string]$serverName, [string]$dbname, [string]$scriptpath)
    {
      [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
      [System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
      $srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
      $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
      $db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
      $db = $srv.Databases[$dbname]
      $scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
      $deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"
    ...
    

    Could you please help me to use script with username and password?

    Thank's!

    BR
    Krzysztof

  • #74252
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Well, you've got a bit happening. For starters, LoadWithPartialName() is deprecated; you really should be using Add-Type to load assemblies. Other than that, this isn't really a PowerShell question – you're using "raw" .NET. I mention that only because a developer site like StackOverflow might get you a better answer, but I'll give it a shot.

    The constructor you're using accepts a server name and assumes your logon credentials have access. An alternate constructor, https://msdn.microsoft.com/en-us/library/ms193784.aspx, accepts a connection object. So you'd need to first create one of those objects. That class' last constructor, https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnection.aspx, accepts a server, username, and password.

You must be logged in to reply to this topic.