MS Sql Server LocalDB connectivity via classic asp [Correct Connection String]

In this post I will show how to make connection string for LocalDB to use with Classic ASP ( Might also work with PHP)


After googling and visiting connection string websites I am still stuck with error like

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Invalid authorization specification
/login.asp, line 19

or

Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/login.asp, line 19

or

ADODB.Connection Microsoft OLE DB Provider for ODBC Drivers error '80004005'
Microsoft OLE DB Provider for ODBC Drivers error '80004005' 
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 


Here is the list of Possible correct connection string

DSN Based  -While setup ODBC DSN I supplied Sql server authentication 

#1 Driver={SQL Server};Data Source=localsqldb;

#2 DSN=localsqldb;Uid=sa;Pwd=urpassword;

Why DNS based : I do not know but all tries were failed until i setup and made 1 successful connectivity with asp application using DSN after that other methods are also start working 

SQLOLEDB Based

#1 Windows authentication , I setup application pool and site identity to  computers username and password
 Provider=SQLOLEDB;Integrated Security=SSPI;Server=np:\\.\pipe\LOCALDB#SH5E512B\tsql\query;  


#2 Sql server authentication 

Provider=SQLOLEDB;Persist Security Info=False;Initial Catalog=urDBName;Data Source=np:\\.\pipe\LOCALDB#SH5E512\tsql\query;User Id=sa;Password=urpassword


To Setup SA for your localdb 

1. Start Navicat and login to DB via Windows authentication as i mention in my last post and run these queries


ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'urpassword' ;
GO


Ref -> http://stackoverflow.com/questions/26403000/how-can-i-setup-my-sql-server-localdb-to-be-as-close-to-a-normal-sql-server

Note : We have navicat so other steps shown on ref page is not required.

Regards

LinWin - Heidisql Navicat connectivity with Microsofts' Sql server LocalDB

In this post I will show how to retrieve connection string for LocalDB to use with Database admin tools

Navicat to  LocalDB Connectivity
Heidisql  to  LocalDB Connectivity

locate SqlLocalDB.exe in win 10 64 bit its located at \Program Files\Microsoft SQL Server\120\Tools\Binn


Start cmd (open command prompt)
Navigate to ->  cd \Program Files\Microsoft SQL Server\120\Tools\Binn

If you want to create new DB then run

> SqlLocalDB create "localsql"  -- here localsql is the name for new instance

After creating DB you need to start it

> SqlLocalDB start "localsql"  -- here localsql is the name for instance which needed to be started

To get the connection string for Navicat or Heidisql

> SqlLocalDB info "localsql"

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB info "localsql"
Name:               localsql
Version:            12.0.2000.8
Shared name:
Owner:              Home\DELL
Auto-create:        No
State:              Running
Last start time:    24-05-2016 01:03:05 PM
Instance pipe name: np:\\.\pipe\LOCALDB#03AD832F\tsql\query

we need to put
Instance pipe name: np:\\.\pipe\LOCALDB#03AD832F\tsql\query
to hostname in navicat or Heidisql



Enjoy :)

LinWin - The First Post Discuss as newbie

Hi Friends,

I started this blog , because i found that lots of knowledge to fix server related issue is still missing and i am bored to create account on lots of site and posting the solution which worked for me.

In this blog i will only post the solution which worked for me and my analysis As i working with E-commerce company i believe these information will beneficial to every one

Techs which is going to be covered are

• AWS
• Azure
• Linux - Centos
• Monitoring Tools 
• Security Tools
• SSL
• CDN
• Mysql
• Ms-Sql
• Oracle
• DB Management Tools
• Caching
• Reporting
• Development tools

In my site no registration is required for posting but i will moderate all the comments , so that we can discuss all the aspects.
Please post all the comment so that even a newbie can just copy paste the solution.

With Regards

LinWin