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¶m2=value'
as GoLangConnectionString
-- sqlserver://username:password@host/instance?param1=value¶m2=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:
- .NET/C#: SqlClient ConnectionString keys and their equivalences
- 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”
- [WayBack] GitHub – denisenkom/go-mssqldb: Microsoft SQL server driver written in go language: Connection Parameters and DSN
- [WayBack] How to test an SQL Server connection | Teusje
- [WayBack] c# – How to get the connection String from a database – Stack Overflow
- [WayBack] Can I view my MSSQL connection string from within SQL Server Management Studio? – Server Fault
–jeroen
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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¶m2=value' | |
| as GoLangConnectionString | |
| — sqlserver://username:password@host/instance?param1=value¶m2=value | |
| — https://github.com/denisenkom/go-mssqldb#connection-parameters-and-dsn | |
| from sys.server_principals | |
| where name = suser_name() |
The Wiert Corner - irregular stream of stuff 
Leave a comment