0

I'm trying to connect to a SQL Server LocalDB instance using the SQL Server Native Client driver. This is because we are looking to use Native Client in our production environment to gain access to the use of subject alternative names on SSL encryption for SQL Server. So far I'm not having much luck - is this a supported scenario? My connection string is:

<add name="Database" connectionString="Driver={SQL Server Native Client};Server=(localdb)\v11.0;Integrated Security=True;AttachDBFileName=|DataDirectory|Database.mdf;" providerName="System.Data.Odbc" />

The error I'm receiving is:

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Any thoughts on how to connect?

Colin Bowern
  • 2,152
  • 1
  • 20
  • 35

3 Answers3

1

In addition to using the 11.0 provider,

  • Did you create an instance of SqlLocalDb?
  • Did you start the instance?
  • If you are not the owner, did you try (localdb)\.\InstanceName?

I recently updated a bunch of content on the following Wiki page:

http://social.technet.microsoft.com/wiki/contents/articles/4609.troubleshoot-sql-server-2012-express-localdb.aspx

Also, your connection string states both native client and ODBC, is this intentional? And are you using AttachDbFilename on purpose?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • For native client do you not use the System.Data.Odbc namespace to connect to it? I'm trying to avoid having to pre-start the instance. Under ADO.NET a simple call to (localdb)\v11.0 with AttachDBFilename will start the instance for me. – Colin Bowern Apr 25 '12 at 15:19
  • @ColinBowern You can use the SQL Native Client through ODBC, OLEDB (although not versions after 11/2012) or the ADO .NET SqlConnection. – ta.speot.is Apr 25 '12 at 20:26
0

You need to use a version of the Native Client that supports LocalDB. You're using version 9. Try:

Driver={SQL Server Native Client 11.0}

Microsoft called this out a long time ago:

Note that because the activation logic lives in the client-side provider, you need to install the latest one - SQL Server Native Client "Denali" (for ODBC and OLE DB) or the next .NET Framework (for ADO.NET).

ta.speot.is
  • 26,914
  • 8
  • 68
  • 96
  • Switching out to the explicit 11.0 driver didn't help. Getting the same error message. Repro'd with a call to new System.Data.Odbc.OdbcConnection(@"Driver={SQL Server Native Client 11.0};Server=(localdb)\v11.0;Integrated Security=True;AttachDBFileName=|DataDirectory|Database.mdf;").Open(); – Colin Bowern Apr 25 '12 at 15:17
  • Ok, so I guess it helps if you have the actual SQL Server 2012 Native Client bits installed. I made the fatal assumption that installing LocalDB would have put those bits in place as well. :( – Colin Bowern Apr 25 '12 at 15:35
  • For the record the final connection string used was Driver={SQL Server Native Client 11.0};Server=(localdb)\v11.0;Integrated Security=True;AttachDBFileName=|DataDirectory|Database.mdf; – Colin Bowern Apr 25 '12 at 15:37
  • @ColinBowern I notice you're using AttachadBFileName. User instances are deprecated, SQL LocalDB is meant to replace them. – ta.speot.is Apr 25 '12 at 20:24
0

Follows how I solve this after 3 days of hard research and trying, with my wishes for Happy Holidays and a great New Year:

  1. on SQL Server Configuration Manager (SQLServerManager11.msc) at scaffolding on left: a. right click on MS SQL Server Native Client(11.0 in my case)'s ‘Client Protocols’ and at right list, right click on TCP/IP for unenabling it allowing enable only 'memory sharing' (as rank order #1) and 'named piping' (as rank order #2); b. stepping down on MSSQL SNC scaffolding, right click on 'Aliases' and at right list, exclude it fully blank; c. you can repeat 'a' and 'b' for MS SQL SNC (32-bits) option of the scaffolding

reason: as a single local client on same machine of SQL Server LocalDb, there is no need for connecting through TCP/IP ports, being memory sharing or named piping more efficient options

  1. on SQL Client Manager (cliconfg.exe) after choosing ‘General’ tab, keep 'Enable protocols in order' list at right unattended, fully blank and mark 'Enable memory sharing protocol' statement; then choose “Alias’ tab making ‘Server alias configuration’ list fully blank as well

reason: same as '1', now configuring client side

  1. piece of cake now: a. start MS SQL SNC by connecting to your localdb\instance - usually (localdb)\v11.0 – when required at SQL Server Management Studio (SSms.exe) starting and attach to wanted database or making this attachment through Visual Studio b. start ODBC Administrator (odbcad32.exe), choose 'User data source' tab, click 'Add', on popup enter database name (generic, to be referred to later), database description (id.), 'localdb\instance' server name, click ‘Next’, ‘Next’ again, now mark ‘Change standard database to’ and at the list choose the wanted database connected and attached on ‘a’, click 'Next', click ‘End’; then on new popup click 'Test data source' for seeing it successful; it is done
greenish
  • 1
  • 1