The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,119 other followers

Simple query to get .NET connection string from current SSMS connection – via: Database Administrators Stack Exchange

Posted by jpluimers on 2020/09/24

I’m connected to database. I use db by Management Studio 2012 Express. Can I check connection string by click something in Management Studio?

[WayBack] sql – How to check connection string in SSMS2012? – Database Administrators Stack Exchange

I adopted the SQL statement in the answer to the above question to:

  • use more common parameter names and values
  • embed strings in quotes
select
    -- part names via https://wiert.me/2012/11/07/netc-sqlclient-connectionstring-keys/
    -- prefer SSPI over True via https://wiert.me/2010/10/19/solution-for-ole-db-provider-connecting-to-sql-server-giving-error-multiple-step-ole-db-operation-generated-errors-check-each-ole-db-status-value-if-available-no-work-was-done/
    'Data Source=''' + @@servername + '''' +
    ';Initial Catalog=''' + db_name() + '''' +
    case type_desc
        when 'WINDOWS_LOGIN' 
        then ';Integrated Security=SSPI'
        else ';User ID=''' + suser_name() + ''';Password='''''
    end 
    as DotNetConnectionString,
    -- note the below need to be URI-encoded later on:
    'sqlserver://' + suser_name() + ':password@' + @@servername + '/' + db_name() + '?param1=value&param2=value'
    as GoLangConnectionString
    -- sqlserver://username:password@host/instance?param1=value&param2=value
    -- https://github.com/denisenkom/go-mssqldb#connection-parameters-and-dsn
from sys.server_principals
where name = suser_name()

You can use this to generate connection strings for use in .NET, OLE DB, Visual Studio Code, go lang and likely many other tools.

Related:

–jeroen

select
part names via https://wiert.me/2012/11/07/netc-sqlclient-connectionstring-keys/
prefer SSPI over True via https://wiert.me/2010/10/19/solution-for-ole-db-provider-connecting-to-sql-server-giving-error-multiple-step-ole-db-operation-generated-errors-check-each-ole-db-status-value-if-available-no-work-was-done/
'Data Source=''' + @@servername + '''' +
';Initial Catalog=''' + db_name() + '''' +
case type_desc
when 'WINDOWS_LOGIN'
then ';Integrated Security=SSPI'
else ';User ID=''' + suser_name() + ''';Password='''''
end
as DotNetConnectionString,
note the below need to be URI-encoded later on:
'sqlserver://' + suser_name() + ':password@' + @@servername + '/' + db_name() + '?param1=value&param2=value'
as GoLangConnectionString
sqlserver://username:password@host/instance?param1=value&param2=value
https://github.com/denisenkom/go-mssqldb#connection-parameters-and-dsn
from sys.server_principals
where name = suser_name()

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: