Connecting to my SQL Server Database from my windows App

Question

Hi, I am making a Windows Application, And I wish for the application to be datadriven.

It is a application that multiple users will have, So I need it to connect to a database remotely.

I already have a SQL Server Instance running on my Dedicated Server, and would like to connect to the SQL Server on my dedi.

I have already checked that it allows remote connections and that port 1433 is open, But I'm not too sure which connectionstrings to add in my App.Config so that my Windows app would connect to my remote database, So for example connect on Server IP 192.168.0.1 Connecting o n port 1433 etc.  Ive tried a couple connectionstrings but none really worked.

This is my old connectionstring I used for a web application i made:

    <add name="GamerConnectionStrings" connectionString="Data Source=HR37348\SQLEXPRESS;Initial Catalog=GamerDB;Integrated Security=True" providerName="System.Data.SqlClient" />

How would I go about editing that connectionstring So that I could copy and paste it to my WPF App.config file, so that It could connect to my remote server.

Thanks

Answer 1

Hello,

Thta far the connection string looks fine, but if you use Ado.Net SqlClient class, you have to remove the "providerName" paramater.

See also http://www.connectionstrings.com/sql-server-2008

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Is your SQL Server the default instance or a named instance?  With a named instiance (like SQLEXPRESS in your old connection string), the port is probably not 1433.  Typically, only the default instance listens on ort port 1433 and named instances listen on a different port.  When you specify an instance name in the connection string, SqlClient queries the SQL Browser service on UDP port 1434 to determine the named instance port number so you must also allow UDP 1434 along with the named instance TCP port number.

If your remote instance is the default instance, simply remove "\SQLEXPRESS" from the connection string.

    <add name="GamerConnectionStrings" connectionString="Data Source=SqlServerName;Initial Catalog=GamerDB;Integrated Security=True" providerName="System.Data.SqlClient" />

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 3

Hi, Yes my database is on a named instance "SQLXPRESS"

And I thought I would have to make major changes to the connection strings, I mean, Wouldnt I need to enter the Server IP Address and the Port, Otherwise How would it know where to connect to? As the server is hosted remotely. If you get what I mean

And yes, I have port 1434 open, Where should I go from here?

Thanks

Answer 4

Please see Dan's reply. You see Data Source=SqlServerName (in your case it is ComputerName\SQLEXPRESS) , so if you have this instances installed as a default,  you do not have specify a port (default 1433). Anyway, try it and post error message back if it is thrown.

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 5

what is the error message you get ?

as the connection string is using integrated security the user needs to have access to the SQL Server Express Instance using his Windows credentials - please check that the user is authorized to access the SQL Server by either being explicitely listened in the Server-> Security -> Login section or by being member of a Windows group being listened in Server->Security -> Login. in both case the login must be mapped to a database user in the database GamerDB too.

Contrary to Olaf posting, the providerName may be necessary depending what you're using to get the database connection. Please tell us how you get the .NET SqlConnection to connect to the database.

Answer 6

Hi, Yes my database is on a named instance "SQLXPRESS"

And I thought I would have to make major changes to the connection strings, I mean, Wouldnt I need to enter the Server IP Address and the Port, Otherwise How would it know where to connect to? As the server is hosted remotely. If you get what I mean

And yes, I have port 1434 open, Where should I go from here?

The only change needed to the original connection string is the Data Source.  This should specify the new server name (or IP address) along with the actual name of the instance.  Assuming your instance name is spelled correctly in your post (SQLXPRESS instead of SQLEXPRESS), the connection string should be:

"Data Source=SqlServerName\SQLXPRESS;Initial Catalog=GamerDB;Integrated Security=True" 

SqlClient locates the server when a named instance is specified as follows:

• Determine server IP address using normal name resolution (e.g. DNS)
• Determine the named instance port number via a UDP query on port 1434 (note this is a UDP port, not a TCP port)
• Connect to the named instance port number returned

TCP port 1433 is not involved here.  You'll need to make sure your named instance TCP port is open as well as UDP port 1434.  The named instance port number will be listed in the SQL Server error log.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 7

Hi guys, I have tried the connectionstrings but still not having much luck connecting to my remote database on my server/

To make things easier, Instead of using a named instance, Should I just make a new default Installation of SQL Server, So I could use the default TCP 1433 to connect, Seems easier.

Would that make it easier, or is it just the same thing?

Thanks

Answer 8

If it is SQL Server EXPRESS Edition you would need to reference it like a named instance even it is the only instance on the server. Can you provide us with the error you are getting from ?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 9

If it is SQL Server EXPRESS Edition you would need to reference it like a named instance even it is the only instance on the server.

Uri, I believe one can still install Express edition as the default instance as long as no default instance already exists.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 10

Yes, it is easiest to install SQL Server as the default instance so that you only need to deal with TCP port 1433.  However, this shouldn't be required.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 11

Hi guys, I have tried the connectionstrings but still not having much luck connecting to my remote database on my server/

To make things easier, Instead of using a named instance, Should I just make a new default Installation of SQL Server, So I could use the default TCP 1433 to connect, Seems easier.

Would that make it easier, or is it just the same thing?

Thanks

could you post the error message you get ?

Answer 12

Hi guys, Its simply not connecting. Im getting an error saying that it cannot connect to the database.

I had another idea... Maybe I could try this way.

I have Management Studio on my server, and on my laptop. So maybe If I tried connecting to the remote database through management studio, (Like in the dialogue box where it says server name) Would I be able to connect to my remote server through Management Studio, and then simply copy and paste the ConnectionStrings from the database in management studio.

Do you think that would work?

And really appreciate the help guys.

Answer 13

Hi Vicande, are you able to connect remotely from SSMS to this instance?  Have you determined yet if it is on the instance side (ex:  Browser service is not running, firewall not configured, etc) or if it is a syntax issue with your connection string?

Thanks,
Sam Lester (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 14

Hi Dan,

I have succeeded to install a SQL Server 2008 R2 With advanced services as a default instance on a Windows 7 Home. I have rubbed off the SQLExpress value proposed ( i have a no-named instance ) .Moreover, i am able to connect to this default instance from a XP Pro SP3 ( with SQL Server authentification as tne Windows 7 is a Home edition ).So you are right

in this case, the DataSource = AD-PC which is the name of the computer on which the SQL Server 2008 R2 is installed ( and the SQL Server service is MSSQLServer

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 15

Hi, Okay I have tried connecting through Management Studio, But it did not work.

This is EXACTLY What I did.

In the servername I put.:

(MyServerIp):1334\SQLEXPRESS

Then for the Authentication, I tried putting Windows, But it would not change my Username, So I changed it to Sql Server Authentication. So in the login name, I tried entering my Server Login and password, Didnt work So I tried typing my server computer name, followed by the password, but still did not work.

Also I have checked the sql configuration, And my SQL Browser is up and running.

Maybe Im doing something wrong?

Answer 16

Hi, Okay I have tried connecting through Management Studio, But it did not work.

This is EXACTLY What I did.

In the servername I put.:

(MyServerIp):1334\SQLEXPRESS

in an earlier posting you've been using SQLXPRESS as your instance name - try just

MyServerIP\SQLXPRESS

as input for the server&instance name.

Use SQL Server Configuration Manager tool to get the correct spelling of your SQL Server instance(s) on your computer.

Answer 17

Ok Its finally saying that it can connect to the server, So im finnally getting somewhere! But then gives me the error:

(A connection was successfully established with the server, but then an error occured during the login process)

All I need to know now is just which login to use. As when I was installing SQL Server I used Windows Authentication Only, I did not create a SQL Server Authentication. Do I need to create a new instance with SQL Server Authentication.

Or which login do I use for my SQLEXPRESS Instance which uses Windows Authentication

Answer 18

Hi Vicande, when you installed SQL Server using Windows Authentication only, you are essentially limiting yourself initially to using that specific account context that setup was run under.  You can enable the sa account after install, but need to be able to first connect using your Windows credentials.  Using SSMS, try to connect to your instance using Windows authentication and the Windows account you used during install.  Once you connect, you can enable the sa password or any other SQL authentication accounts you'll need.

Thanks,
Sam Lester (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.