connect to database on server

Question

1.00/5 (1 vote)

See more:

hello,

I've c# app ,and I want to connect to sql server on other computer. I used the string connection in the database and put it in my app, but it didn't work? Also there're connected through switch?

Posted 27-Sep-12 0:00am

Member 8584763

Add a Solution

Comments

Malli_S 27-Sep-12 6:04am

What error you are getting? What do you mean by 'didn't work' ? Add some more details/code snippets to your question.

Moreover, are you able to ping to the remote machine from your local machine? Is database configure to access the remote users?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2012-09-27T00:05:00

Solution 1

Are you sure your connection string is correct? A switch won't hinder you in accessing the database, but a router, a firewall could.
- Check if the TCP/IP connections is enabled for the server instance
- Check the firewall setting throughout the hops between the client and the server
- Check the connection with an other tool

Posted 27-Sep-12 0:05am

Zoltán Zörgő

Comments

Member 8584763 27-Sep-12 6:09am

the connection string is correct
-TCP/IP is enabled.
-firewall ,I did open a port for remote access.
-if I tried connect them directly will it make a different

Zoltán Zörgő 27-Sep-12 6:55am

What is the exact error message you get?
Take a look with process explorer or other tool to see the tcp level connection status on both the server and the client side

Member 8584763 27-Sep-12 7:01am

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

System.Exception {System.Data.SqlClient.SqlException}

Zoltán Zörgő 27-Sep-12 7:13am

This could be anything.
- Is the client and the server on the same LAN (physical, vlan, address space)
- can you ping the server from the client using the same name you added in the connection string
- can you ping it via it's ip?
- download portqry (http://www.microsoft.com/en-us/download/details.aspx?id=17148) and try to query the 1433 port
- read also this article about connectivity and firewall related settings: http://support.microsoft.com/kb/287932
- use a network sniffer like microsoft network monitor or whireshark to track network traffic between them

...but I have the feeling, you are treating some basic requirement as trivial but it is no. I suggest, you rethink and retest everything step by step

Member 8584763 27-Sep-12 7:50am

I tried a switch and connect them directly .
How to ping the server

Zoltán Zörgő 27-Sep-12 8:00am

Wow! You don't know how to ping the server? Are you sure you have enough networking knowledge? Pingign is the very basic networking diagnostic tool ever: open a command prompt, and type ping fully-quelified-servername (+Enter), after that tray ping short-servername (+Enter) and then ping serverip (+Enter). You should see some milliseconds displayed. Of course use the proper names and ip.

Member 8584763 27-Sep-12 8:13am

it's said time out, could it be that I connect them directly.

Zoltán Zörgő 27-Sep-12 8:18am

Timeout is just timeout. Means that the connection ca not be established. But if you don't check if low level tcp/ip connection is working or not, other high level test are useless.
Please check everything I proposed. If you don't follow the instructions, I can not help you.
Sorry, but I have no time to argue with you.

Member 8584763 27-Sep-12 8:20am

just this is there a different If I connect them directly through cable or switch?

Zoltán Zörgő 27-Sep-12 8:24am

Of course. It depends on the network cable or the NIC itself. If the adapters don't support "auto mdix" you will need a cross cable to connect two PC-s directly. Type arp -a in command prompt to see if connection is established or not. That ping the parties from each other.

Member 8584763 27-Sep-12 8:44am

I used cat-6 in that case it won't work ,right?

Zoltán Zörgő 27-Sep-12 8:45am

CatX is an other thing. Ethernet crossover is a wiring terminus: http://en.wikipedia.org/wiki/Ethernet_crossover_cable

Member 8584763 27-Sep-12 8:50am

then I should use switch in that case

Zoltán Zörgő 27-Sep-12 9:13am

If you have one, you should use it.

Member 8584763 27-Sep-12 9:19am

OK, I should change the Ip address in client and put like the server IP, right?

Zoltán Zörgő 27-Sep-12 12:17pm

Well, you should use the same netmask, like:
Server: 192.168.1.101/255.255.255.0
Client: 192.168.1.102/255.255.255.0

Member 8584763 27-Sep-12 22:35pm

Well, I put fix IP address for the server and fix IP address for the client , and I tried to ping the server from the client ,but it said request time out. they both have the netmask. and ip address just like you put them.
So , what do you think is the problem ?

Member 8584763 28-Sep-12 1:22am

I manged to make the client ping the server ,but the server doesn't ping the client , do you think it's because of the firewall?

Zoltán Zörgő 28-Sep-12 2:40am

Yes. If you have unidirectional working ping, it's because ICMP is filtered by the firewall on the "unpingable" party. I suggest you disable the firewall for the start.

Member 8584763 28-Sep-12 3:18am

finally , I manged to ping them , but still when I try connect my c# app to sql database server. it gives me the same message, what do you think is the problem?

Zoltán Zörgő 28-Sep-12 4:05am

1) Go trough this to be sure that tcp/ip is enabled for sql server: http://msdn.microsoft.com/en-us/library/bb909712(v=vs.90).aspx
2) After that read my former reply how to check port connectivity and tcp level communication
3) Post your connection string here.

Member 8584763 28-Sep-12 6:01am

the first step is done , the second I download it ,but when I run it .It despair all the time.
My connection string is a class which has my string connection:
internal static string x = @"Data Source=.\sqlser;Initial Catalog=SuperMarket;Integrated Security=True";

Zoltán Zörgő 28-Sep-12 7:09am

The portqry is a console app, run it from the command prompt.
Well, this connection string won't work. "." is the local host, you have to use the server ip instead. Integrated security will work only in a domain with proper users or on localhost. Use sql authentication instead with a dedicated sql server user.

Member 8584763 28-Sep-12 7:15am

I changed the connection string to:
data source=192.168.1.100;initial catalog=supermarket;integrated security=true;uid=;pwd;timeout.
it said can't connect to the ipaddress. Error number 18456

Zoltán Zörgő 28-Sep-12 8:53am

You can use ipaddress only, if you have default instance. If not, you have to specify instance: "ip\instance". But integrated authentication works only in situations I have already drafted.

Member 8584763 28-Sep-12 10:24am

I tried login from client sql server to server sql server. using IP address of the server, I create user name and password for sql authentication, but still getting error code 184556. what do you think..

Aarti Meswania · Answer 2 · 2012-09-27T01:05:00

Solution 2

Connect SQL server from remote pc

1. share your database on LAN/WAN
2. set new connection string in exe/text file [where sql connection string is written]
 
step 1: follow steps given in this link [For access sql database remotely(from different pcs)]
http://support.microsoft.com/kb/914277[^]
 
step 2: after step 1 complete then change connection string's parameters, 
datasource = server-name OR IP-Address [which is in sharing now after step 1] ;
Initial catalog = your database name ;
UserId = sql server - user login Id ;
Password = sql server - user login password;

If error comes (err No: 18456) - can not connect from remote pc User not found in Domain
Reason : UserLock out (state-1)
sql server does not contain loginName like this...
this username(e.g. sa) is not created inside this server-name
Solution : create New Login in sql server-(With SQL authentication Mode)

Create New SQL Server Authenticate user login

sql -> object explorer ->security -> logins-> new login->

in new window
general tab-> Login name : abcd
                        check 'Sql server authentication'
                        Pwd =*** , confirm pwd=***
                        check 'Enforce pwd policy'
                        DO NOT check 'Enforce pwd expiration' (if check it will cause err: 18515)
Ok.
.
.
.
now, try to login
.
If err comes (err No: 18452)

solution : For sql 2000
                         start -> Program ->  sql server  -> enterprise Manager ->
                         server name -> properties ->security under authentication,
                         check 'Sql server & Windows'
           For sql 2005
                        sql -> servernm -> properties -> security ->
                        check 'Sql server & window Authentication mode'
Note: stop sql server & Agent services then start both services again.
     
Now disconnect sql server & connect again you will also able to Login through sql-authentication

Happy Coding!
:)

Posted 27-Sep-12 1:05am

Aarti Meswania

Updated 28-Sep-12 1:47am

v3

Comments

Member 8584763 27-Sep-12 7:42am

I did almost exactly like it said in step one except

In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you want to configure.

This time I connect them directly , I still get the same message

Aarti Meswania 27-Sep-12 7:54am

try to login on server using other pc which have sql
find ip address of server pc using command-prompt ipConfig command and use that
1. server
2. client
from client pc's sql-server try to login on server pc's sql
it will be easy to test
and also make sure that Firewall settings are configured properly (you have added exes in Exception-tab) and sql-agent service is running on server-pc

Member 8584763 27-Sep-12 7:58am

last step isn't clear
it will be easy to test
and also make sure that Firewall settings are configured properly (you have added exes in Exception-tab) and sql-agent service is running on server-pc

Aarti Meswania 27-Sep-12 8:00am

have you done firewall settings on server?

Member 8584763 27-Sep-12 8:01am

yes

Aarti Meswania 27-Sep-12 8:07am

ok, firewall is off
and in exeption tab SQL-Browser and SQL-server exes is added.

if yes then,
sql server config manager
set protocols same as document
and do not forget to restart all sql services

after that you are not able to connect then,
create a new user login in sql server and use that login name and password

Member 8584763 27-Sep-12 8:16am

what do you mean exeption tab SQL-Browser and SQL-server exes is added.

Aarti Meswania 27-Sep-12 8:24am

it is necessary without follow that you can not login from remote server
follow instruction given in
http://support.microsoft.com/kb/914277#method3

Member 8584763 27-Sep-12 9:00am

I did the three steps , still got the same problem

Aarti Meswania 27-Sep-12 9:00am

have u restart sql agent service successfully?

Member 8584763 27-Sep-12 9:07am

still the same

Aarti Meswania 27-Sep-12 9:09am

how do you check?
from other pc's SQL-server?
and what are you using IP address or servername
also tell me error with it's code

Member 8584763 27-Sep-12 9:10am

I ping the server Ip from the client .It said time out

Aarti Meswania 27-Sep-12 9:23am

ok then client & server pc are not connected properly in LAN/WAN
check your internet/LAN cable

Member 8584763 27-Sep-12 9:50am

I checked ,but it still the same , do you think I should put the server IP address in the client internet protocol?

Member 8584763 28-Sep-12 6:02am

I did exactly like you said, when I run the app it said there's not like userId

Aarti Meswania 28-Sep-12 6:07am

your client & server is connected now?
means ping is giving now positive response?

Member 8584763 28-Sep-12 6:09am

yes, I can ping them both using fix IP address

Aarti Meswania 28-Sep-12 6:11am

ok then, are you trying to connect from sql server or vb.net?

Member 8584763 28-Sep-12 6:12am

I trying to connect from c# to sql server.

Aarti Meswania 28-Sep-12 6:15am

paste here your connection string (not need IP-address left it blank for security)

Member 8584763 28-Sep-12 6:16am

My connection string is a class which has my string connection:
internal static string x = @"Data Source=.\sqlser;Initial Catalog=SuperMarket;Integrated Security=True";

Aarti Meswania 28-Sep-12 6:19am

Data Source=IPAddress (e.g 10.0.0.1);Initial Catalog=SuperMarket;
Uid=sa;
pwd=abc;

connection string should be like above,
please login using userid and password

Member 8584763 28-Sep-12 6:29am

it said time out??

Aarti Meswania 28-Sep-12 6:32am

also include below line in connection string
timeout=100000;

do you have sql server on client pc?
it will be easier to test
and when client pc's sql server connect with server then copy that parameters for using in connection string

Member 8584763 28-Sep-12 6:41am

it said login failed ,the login from untrusted domain. I have , but the last part is not clear.

Aarti Meswania 28-Sep-12 6:44am

open sql server on client pc
servername=ipaddres
tick SQLAuthentication
insert username
insert password
now press connect

and if any error comes then it will give Specific error code
give me that error code

Member 8584763 28-Sep-12 6:52am

it said can't connect to the ipaddress. Error number 18456

Member 8584763 28-Sep-12 7:16am

I changed server name insted of ip address I put it the server name ,it give error 26

Aarti Meswania 28-Sep-12 7:35am

use Ipaddress not server name
and see updated solution for solve error no. 18456

Member 8584763 28-Sep-12 7:54am

I still get the same message .I'll try to change client PC to check...

Aarti Meswania 28-Sep-12 7:56am

have you create new login as per mentioned above with same settings?

then use that new userid-pwd for login

and use ipaddress instead of servername

Member 8584763 28-Sep-12 8:32am

I did it all ,but it's the same message. what do you think is the problem..

Aarti Meswania 28-Sep-12 8:37am

actually it is not only solution, I have faced this thing. error nos- solutions everything. and after doing all this i was able to connect with sql server from remote pc,

may be some step you missed
I think you should check it properly
I had faced same situation like you and this was worked for me, and that's why I have note-down this proccess in my diary

:)

Member 8584763 28-Sep-12 10:20am

I changed server-pc, still getting the same error 18456

Member 8584763 29-Sep-12 6:27am

I think , the problem in the first step, when I did . I didn't do completely .Because there's no document to explain this step cause I'm using sql server 2012 the step is:
On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.