SQL Server

Everything SQL

  • SQL Server

    Powershell Create of the SSISDB Catalog

    January 23, 2020 / No Comments

    So when management gets this [insert explicative] idea to strip away my MSDN enterprise access, and I need to create an SSISDB catalog on my dev server- what the hell am I to do? Well, maybe not simple… I put on my PowerShell hack through hat. Shall we?

    In your favorite SQL instance (we’ll just use localhost for my examples) start with making sure you have CLR ENABLED first:

    EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO

    Do this in a query window in SSMS of course.

    Now the PowerShell:
     

    # Load the IntegrationServices Assembly  
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")  

# Store the IntegrationServices Assembly namespace to avoid typing it every time  
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"  

Write-Host "Connecting to server ..."  

# Create a connection to the server  
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"  
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString  

# Create the Integration Services object  
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection  

# Provision a new SSIS Catalog  
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "P@assword1")  
$catalog.Create()

    The last part in “Provision a new SSIS Catalog” make sure you do a good password.

    And that’s it…. So why am I doing this with PowerShell? No “DIS” on PowerShell, but it seems that the version I install SQL Server 2016 has this thing about connecting to Azure SSIS with creating a SSISDB Catalog, and since I no longer have access to MSDN (you knew MSDN was in this somewhere) I had to hack my way around getting a catalog created… Now yes, I could have done the Azure SQL SSISDB connect bah bah (I do have an enterprise Azure accounts), but you what? This same management does not want to spend money on Azure… YA! An Azure SQL SSISDB has a cost. 

    So there you go… Got to love this jumping around not having the tools to get stuff done.

    ~ScottGeek

     

     

     

     

    Read Full Post
  • IIS,  SQL Server

    The SQL Server Windows credentialing to work with ASP.Core and other IIS Web Sites

    February 8, 2019 / No Comments

    So somewhere in the backwaters of connecting an IIS hosted web app (a.k.a ASP Core 2.x SPA like app that uses SQL Server as it’s data host), we find the secret not so secret way to wire up a Windows Credential for Database access.

    Write this down: 

    IIS AppPool\DefaultAppPool  or Generically   IIS AppPool\<Name My Pool>

    We start here in IIS with our favorite App Pool- 

    Now this one is just for example… an ASP Core app pool, of course, would have a No Managed Code like this:

    Either way, the SQL Windows credential set up is the same….
    In SQL server the new login looks like this: Note the IIS AppPool\My Favorite App Pool

    If the database already exists, you can select the default database at this time…

    Now a word about SQL Roles:

    Before creating the new SQL Login – one should add at least add the dbcreator role for this login. It will be important if you plan to use EF Core Migrations, and of course if you want to have the new web site automatically create and seed a new db instance as well as perform and EF Core Migration.  Don’t skip this step if you intend to do continuous deployment.

    Now the last bit is the connection wire up in your Web App:

    In your appsettings.json file, normally you set a default connection string… it should look something like this:

    “DefaultConnection”: “Server=<Your Server SQL Instance>;Database=<My AppDatabase>;Trusted_Connection=True;MultipleActiveResultSets=true” 

    Now this connection string does vary but basically– Server=   Database= and Trusted_Connection=True are the important parts… the other parameters are optional.

    Now, notice no “User Id=” or “Password=” are in the string. Well, that is the point. You certainly can set up a SQL user and password for your database. But the whole point of setting the app pool windows credential in SQL was to avoid putting passwords in a appsettings file.  

    One last matter- and it can drive you crazy. When you publish your web site (or app) to IIS, make sure your actual App pool is assigned correctly. I know it’s like a “DUH” moment here. But your App Pool/SQL Credential/Web Connection string need to be aligned. Or guess what? Indeed, connecting errors will be in your path… or way depending on how you look at it.

    Now back to Web App’ing 

    ~SG

    Read Full Post