0

VBA - SQL Server 2014 - How to access localhost?

Pretty much, I need to access localhost of SQL Server and I have tried almost anything I found here:

What is the sql connection string I need to use to access localhost\SQLEXPRESS with Windows Authentication or SQL Authentication?

I have tried this:

    str_connection_string = "Data Source=localhost\SQLEXPRESS;Initial Catalog=Tempt;Integrated Security=True;"
    str_connection_string = "Data Source = .\\\\SQLEXPRESS;Initial Catalog=Tempt;Integrated Security=True;"

And both return error:

enter image description here

This is a German Run-Time error, saying something about OLE DB. (My German is also not in my top 4 languages).

I want WindowsAuthetication. The automatic one, without username or password.

So what are the ideas? I use MSSQL Management Studio 2014. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

A day has passed. this is what I tried in the morning: @GuidoG I liked the idea, but I did not succeed to make it in a way. What should be the servername on the top (point 1), in order to find my DB (point 3)?

enter image description here

@Comintern The local server, to which I am connecting through the MS DB Studio looks like this:

(localdb)\MSSQLLocalDB

Edit: I am trying to connect to localhost.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • "Data Source= **YOUR_COMPUTER_NAME** \SQLEXPRESS;Initial Catalog=Tempt;Integrated Security=True;" – Comintern Sep 06 '16 at 15:31
  • Hi. It really does not work. Same mistake. I have taken the YOUR_COMPUTER_NAME from here >Systemsteuerung\Alle Systemsteuerungselemente\System – Vityata Sep 06 '16 at 15:46
  • 1
    create a txt file. Rename its extension to .UDL Now doubleclick on it and you will get a screen where you can build and test your connectionstring. If you have a tested connectionstring than save it and then open the text file with notepad to see the completed connectionstring – GuidoG Sep 06 '16 at 15:53
  • What does the "SERVER\INSTANCE" look like when you connect to the database engine in SSMS? – Comintern Sep 06 '16 at 15:53
  • @Comintern see the update of the post. – Vityata Sep 07 '16 at 08:02
  • @GuidoG - see the update of the post. – Vityata Sep 07 '16 at 08:02
  • 1
    You are using an ADO.NET connection string. OLEDB connection strings *have* to specify the driver they want to use – Panagiotis Kanavos Sep 07 '16 at 08:12
  • 1
    Possible duplicate of [How do I connect to SQL Server using OLEDB using Windows Authentication connection string](http://stackoverflow.com/questions/6913409/how-do-i-connect-to-sql-server-using-oledb-using-windows-authentication-connecti) – Panagiotis Kanavos Sep 07 '16 at 08:14
  • Can you show us the first tab of this screen ? What are you using there ? OLE DB for SQL Server or Sql Server Native Client ? For local db it should be the latter – GuidoG Sep 07 '16 at 08:14
  • Now I am using the Sql Server Native Client. With a server name "MSSQLLocalDB" I cannot find any db to select. And what should I write in the Server SPN? – Vityata Sep 07 '16 at 08:27
  • In localDB you cannot select a database from the server, you have to use second option at the bottom of the screen and select your mdf file – GuidoG Sep 07 '16 at 08:41

2 Answers2

1

Try stating the driver like the below in the VBA connection string. You will find server\instance right below the connect bar (in object explorer) in SSMS.

"SERVER=[server]\[instance];DRIVER={SQL Server};Initial Catalog=[database];Trusted_Connection=Yes"
Kevin
  • 453
  • 2
  • 10
  • 1
    I think you posted an ODBC connection string. OLEDB connection strings require a `Provider` parameter instead of `DRIVER` – Panagiotis Kanavos Sep 07 '16 at 08:13
  • True that, @Panagiotis KAnavos. I am until here:https://sysmod.wordpress.com/2014/07/11/adodb-connection-string-for-localdb-and-sql-server-native-client/ And I am looking for Provider. Any idea? – Vityata Sep 07 '16 at 08:21
  • For localDB the provider should be a "SQL Server Native Client". If you dont have this you can download it from microsoft but it takes some searching – GuidoG Sep 07 '16 at 08:23
  • I am trying this: >Provider=SQL Server Native Client;Server=(localdb)\MSSQLLocalDB;Initial Catalog=Tempt;Trusted_Connection=Yes;< but it does not work. – Vityata Sep 07 '16 at 08:25
  • @Vityata did you try googling for `OLEDB SQL Server Connection String" ? Or check the documentation? There are a lot of resources, eg [this one](https://connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/) – Panagiotis Kanavos Sep 07 '16 at 08:26
  • well, I did not find a way to refer a local db in the link. – Vityata Sep 07 '16 at 08:30
1

Actually this is what has worked for me:

connectionString = "Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Initial Catalog=Tempt;Trusted_Connection=yes;timeout=30;"

My db is named "Tempt". I have taken the code from here: https://sysmod.wordpress.com/2014/07/11/adodb-connection-string-for-localdb-and-sql-server-native-client/

Thanks everyone for the help! :)

Vityata
  • 42,633
  • 8
  • 55
  • 100