Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to connect to a remote database from a C# application

Posted on 2009-05-03
17
Medium Priority
?
843 Views
Last Modified: 2012-05-06
Hello,

I am developing a C# application, the user should be subscripted to use the program. The program should query if the username and password is correct from a remote database. I'm using MS SQL Server 2008 as the database. How can I first establish a connection, -do I need to change some permissions, open ports etc- and then how can I retrieve the query result? Can you give me a clear explanation or a link?

Thanks in advance.
0
Comment
Question by:guveniscan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
17 Comments
 
LVL 6

Accepted Solution

by:
Cebik earned 2000 total points
ID: 24290546
On machine where you have mssql installed make shure that you have enabled TCP/IP in SQL Server Configuration Manager -> SQL server xxxx network configurator -> Protocols for..

TCP/IP = enabled
named pipes = enabled (i'm not sure if this one is needed)

add exception in firewall - TCP 1433 for both machines

//connecting
//set source, dbname, username and passwd before
 
Console.WriteLine("Data Source     = " + source); //"(local)" or "computername"
Console.WriteLine("Initial catalog = " + dbname);
Console.WriteLine("User ID         = " + username);
Console.WriteLine("User password   = " + passwd);
 
SqlConnectionStringBuilder dbInfo = new SqlConnectionStringBuilder();
string connectionString = "Data Source=" + source + ";Initial catalog=" + dbname + ";User ID=" + username + ";Persist Security Info=True;";
string pass = passwd;
 
dbInfo.ConnectionString = connectionString;
dbInfo.Password = pass;
                
SqlConnection mssql = new SqlConnection();
mssql.ConnectionString = dbInfo.ConnectionString;
mssql.Open();
 
//readingSqlCommand mpCmd = mssql.CreateCommand();
mpCmd.CommandText =
      "SELECT object_id from s4.objects where class_id = 17";
 
SqlDataReader mpRdr = mpCmd.ExecuteReader();
while (mpRdr.Read())
{
    int id = mpRdr.GetInt32(0);
    Console.Writelind(id.ToString());
}
 
mpRdr.Dispose();
mpCmd.Dispose();
mpRdr.Close();

Open in new window

0
 
LVL 6

Expert Comment

by:Cebik
ID: 24290556
sorry..
line 20

//reading
SqlCommand mpCmd = mssql.CreateCommand();

Open in new window

0
 
LVL 6

Expert Comment

by:Cebik
ID: 24290610
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:guveniscan
ID: 24291572
Thanks for your concern. Now It is giving the SQL Exception in mssql.open line.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

I enabled TCP/IP and named pipes, WIndows Firewall is turned off, port 1433 is open and sql browser service is started.

I'm using the same computer for client and server machines  and I'm using Windows Authentication, Can they be the problems?
0
 
LVL 6

Expert Comment

by:Cebik
ID: 24292824
i think yes.. add some user or make mixed mode auth.. (user: sa)
0
 
LVL 6

Expert Comment

by:Cebik
ID: 24292845
adding user
osql -E -Q sp_addogin username, userpasswd

but more efficient (like windows auth) is mixed mode
0
 

Author Comment

by:guveniscan
ID: 24292974
I have enabled the mixed mode through Server Properties->Security->Server Authentication. I've added a new login (with public privileges) from the GUI and am able to login with this user in Management Studio. But I still cannot establish a remote connection from the client application. It still raises exception in mssql.open line.
0
 
LVL 6

Expert Comment

by:Cebik
ID: 24293027
when you enable tcp/ip you must restart mssql services or reset computer..
and show me your connection string
PS (local) or 127.0.0.1
0
 
LVL 6

Expert Comment

by:Cebik
ID: 24293079
you must have also SQL Server and SQL Server Browser runung..
start them if it isn't started (start and stop this if you change settings below)
i've got all enabled in protocols (shared memory, named pipes, tcp/ip, via)
0
 

Author Comment

by:guveniscan
ID: 24293087
I restarted the services when I changed smt. My connection string is below.

"Data Source=81.215.xxx.xx;Initial catalog=MyDatabaseName;User ID=UserID;Persist Security Info=True;"

I'm using the code you provided, password is added in the later line "dbInfo.password"

I have SQL Server 2008 installed and SQL Server Express 2005 has come with Visual Studio. Should I modify the data source to smt like 81.215.xxx.xx\servername\instancename ?

0
 
LVL 6

Expert Comment

by:Cebik
ID: 24293099
yes..
if this instance isn't default instance then you must add it..
i will check where :)
0
 

Author Comment

by:guveniscan
ID: 24293116
I have shared memory, named pipes, tcp/ip enabled. But when VIA is enabled I cannot restart SQLServer service. It gives an error, I should disable it to restart the service.
0
 
LVL 6

Expert Comment

by:Cebik
ID: 24293145
i dont think so... live it disabled then..
and...

DataSource=".\SQLinstanceNAME"
or
DataSource="172.16.10.200\SQLinstanceName"
or
DataSource="computerName\SQLinstanceName"
or
Data Source=.\SQLExpress;Initial catalog=dbName;User ID=sa;Persist Security Info=True;

or
Change SQL Instance name to default(local) instance
0
 
LVL 6

Expert Comment

by:Cebik
ID: 24293153
As far as I know we cannot change/switch a default instance with  a named
 one. Re-install SQL Server only.
0
 

Author Comment

by:guveniscan
ID: 24293816
The program is working in another network location. I guess the problem is about my router, I'm using DSL connection. My friend told me I have to play with NAT settings, so the router can forward the request to the server machine.  
0
 
LVL 6

Expert Comment

by:Cebik
ID: 24293838
shure..

try first this on the same machine

when it will be work than you can forward required port
0
 
LVL 6

Expert Comment

by:Cebik
ID: 24293845
you can try to test connection from command line..
install mssql express or other..

and try to login to second computer database:

osql -ScomputernameOrIP -Usa -Ppassword

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question