Connection String via IP Address For SQL Server

Question

I want to create connection string so that I can connect to SQL database remotely in my C# code using OleDb as a provider and the server IP address.

Note 1 : I'm using OleDb because I want to handle different database types. SQL is just an example. Note 2 : The database resides on another machine (machine on another network)

I did all the setup to connect from another machine (firewall,Enable TCP/IP..etc), and now I can connect remotely using Microsoft SQL Server Management 2014 by specifying (server name : My Computer Name-PC\Instance name) and using SQL Authentication then entering the username and password and press connect and then it goes well, I connect successfully.

BUT I TRIED A LOT OF COMBINATIONS TO BUILD THE CONNECTION STRING IN MY C# CODE AND NONE OF THEM WORKS EXCEPT THIS :

OleDbConnection conn = new OleDbConnection(@"provider = sqloledb; data source = MyCompName-PC\sqlexpress; Initial Catalog = DataBase1 ; user id = MyUsername ; password = MyPassword ;");

Otherwise if I try to use my server public IP address instead of MyCompName in Data Source it keeps giving me error : server not found or access denied.

I search in connectionstrings.com but problem is still there.

Well if it works where is the problem ?

Julien
– Julien

2015-05-29 14:27:19 +00:00
Commented May 29, 2015 at 14:27 — Julien
– Julien, Commented May 29, 2015 at 14:27

Rahul · Accepted Answer · 2015-05-29 14:36:55Z

2

From your post it looks like you are trying to connect to a SQL Server database then why are you using OleDbConnection? instead of using SQL Server connection provider.

OleDbConnection connection provider is used for connecting to MS Access database.

You should be using a SqlConnection class like

SqlConnection conn = new SqlConnection("data source = MyCompName-PC\sqlexpress; Initial Catalog = DataBase1 ; user id = MyUsername ; password = MyPassword ;")

See SQLConnection Reference for more information as commented by @AlexK.

edited May 29, 2015 at 14:36

answered May 29, 2015 at 14:27

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rahul Over a year ago

Thanks @AlexK. included that link in answer.

Mahmoud Ghandour Over a year ago

I'm using OleDb because I want to handle different Database types. I'm just using SQL DB in my question as an example. And I want to use IP addresses in data source so that I can connect to the DB when it's on another network. Imagine the SQL DB resides on a server somewhere in US and my client want to connect to it from his PC in Europe . What's the connection string to do such a thing ? that's the question.

Rahul Over a year ago

No, that's not a recommended as well as best way. If you really want to handle different DB types then try using Abstract DB factory pattern but use the exact DB provider,

Icemanind Over a year ago

Almost every popular database engine on the market today (SQL Server, Oracle, MySql, Sqlite, PostgreSql etc...) have ADO.Net connectors. I still don't get why you'd want to use OleDbConnection. In addition to all that, the ADO.Net providers provide you with a ConnectionBuilder class that would handle all this connection string stuff for you.

Collectives™ on Stack Overflow

Connection String via IP Address For SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related