Cannot connect to MS SQL Server

Hello,

A client of mine has a software that they were provided by a garment supplier company with whom they have a tie-up for selling garments.

This client is using my developed software for last 3 years. Now they want me to integrate the software with the one they are provided by the supplier product company as the software provided is not as per their requirements.

The twist starts here :)

It so happens that my client has a small LAN of 5 PCs. Their supplier company's technicians have installed MS SQL Server on the server PC in LAN and have installed the software in rest 4 PCs (nodes). This (supplier company's) software is able to connect to the server (MS SQL Server) and works properly but my developed software is not able to connect to MS SQL server from the nodes.

I have tried to connect using both modes Windows Authentication as well as Server Authentication modes but to no avail.

Here is the code that I am using to try to connect:

Code:

---

Dim adoCn As New Connection

'connect to SQL Server
adoCn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=192.168.0.54\SQLEXPRESS;Initial Catalog=massretail;User Id=sa;Password=sa;"
adoCn.Open  '<== The connection fails always Why? :confused:

---

In case of above code I keep on getting Connection refused by Server.

But if my software is run on PC where SQL Server is installed it works with following code:

Code:

---

adoCn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=massretail;Data Source=.\SQLEXPRESS"

---

I don't know as to how to solve this problem. Can someone help me out please.

TIA

Yogi Yang

This http://www.connectionstrings.com/def...twork-protocol suggests you need something like:

Code:

---

Data Source=192.168.0.54,1433;Network Library=DBMSSOCN;
Initial Catalog=massretail;User ID=sa;Password=sa;

---

Do you know what credentials the other app is using to connect? Are you able to connect to other databases using the same credentials as you're using to connect to this one?

Thread moved to the Database Development forum.

SQL Server Express does not allow remote connections by default. The other app might have solved that by not communicating with the database directly but with some other server program installed on the same machine that hosts the Express database. You can however configure the server to accept remote connections, see this KB article for more information.

Quote:

---

Originally Posted by Joacim Andersson

Thread moved to the Database Development forum.

SQL Server Express does not allow remote connections by default. The other app might have solved that by not communicating with the database directly but with some other server program installed on the same machine that hosts the Express database. You can however configure the server to accept remote connections, see this KB article for more information.

---

I know that. But the SQL Server is configured to allow remote connection.

Quote:

---

Originally Posted by jmcilhinney

Do you know what credentials the other app is using to connect? Are you able to connect to other databases using the same credentials as you're using to connect to this one?

---

I don't know as to how the other APP is communicating. I did ask my client to contact the company and try to get the info but they were not provided with this info.

I also tried to connect to remote SQL Server using MS SQL Server Management Studio Express but this also fails.

Finally the software supplied is build in .NET.

Regards,

Yogi Yang

1) "Finally the software supplied is build in .NET." -- are you talking about YOUR app, or the other app?
2) Soo.... you used SQL Security from the remote machine and it failed, but used Windows security from the local machine and it WORKED?
3) Really? "sa" "sa" as the username & password????

...

1) If YOUR app is in .NET you should be using ADO.NET, not ADO classic.
2) Have you tried verifying the security credentials on the machine running SQLServer?
3) YEah, I'm hoping that that WOULD fail... IF it was set up "correctly" there IS no "sa" account...


-tg

Quote:

---

Originally Posted by techgnome

1) "Finally the software supplied is build in .NET." -- are you talking about YOUR app, or the other app?

---

The app supplied to my client by their supplier is built in .NET

I would start be researching the error code the server is returning to you, that's a good starting point.

I would make sure the instance you're trying to connect is running, tcp/ip is enabled

Quote:

---

Originally Posted by yogiyang

The app supplied to my client by their supplier is built in .NET

---

I started to reply that this is largely irrelevant... but it might actually help... look in the folder where it's installed, see if you can find the config file... open it and see if you can find the connection string in there... use that.


-tg

I checked that out also.

It is just storing the IP of the server PC in registry nothing else.

Regards,

Yogi Yang

Ok - the only thing you should be trying to do is connect with SSMS - do not try your own connection in code.

So with that said - can you login to the actual SERVER that is running the SQL SERVICE?

And if you can - have you tried SSMS from that machine itself?

I tried SSMS directly with its default settings as well as user settings. But I keep getting connection errors.

Due to some unknow reasons I am not able to attach the screen shots to my post so I cannot show the screen shots at the moment.

Will try to put the images to some hosting service and then point the links here.

TIA

Yogi Yang

If you make your screen shot a JPG it can be UPLOADED with a post on the forum here.

And to clarify - you are saying you cannot connect to the SERVICE from the ACTUAL MACHINE that it is running on?

This isn't anything 'silly' like a Firewall getting in the way is it ?

Error I get in MS SQL Server Management Studio Express
Attachment 105053

The error I get from my software
Attachment 105055

Please help.

TIA

Yogi Yang

Are you saying that you are logged into the actual machine called FCC5011292 - with remote desktop - or physically at the console?

Is this a domain?

Is the DB encrypted?

Have you talked about your issue with the other vendor - or are you trying to do this without talking with them?

Quote:

---

Originally Posted by szlamany

Are you saying that you are logged into the actual machine called FCC5011292 - with remote desktop - or physically at the console?

Is this a domain?

Is the DB encrypted?

Have you talked about your issue with the other vendor - or are you trying to do this without talking with them?

---

I did try to talk with the vendor but the vendor is not replying to my queries.

I am trying to access the MS SQL Server from another PC from the network of my client. On this machine my clients vendor provided software runs like a charm and connects immediately to MS SQL Server but MS SQL Server Management Studio Express also cannot connect to MS SQL Server. I don't understand as to why? I have tried to connect using Windows Authentication mode as well as User Authentication mode and both cases it fails with the same error message.

If my previous post have not made it clear my software is developed in VB6.

Regards,

Yogi Yang

Ok - I'm trying to help you debug this situation - of which I've encountered similar ones for decades.

First thing you must do is CONNECT to the ACTUAL MACHINE that is hosting the SERVICE. Get on the BOX - use REMOTE DESKTOP - then make a connection with SSMS.

#1 - have you done that yet?

#2 - can you do that?

#3 - why won't you do this?

Please answer these 3 questions. We can help you in a matter of minutes if you would only do this simple step first!!!!

PLEASE!!!!!!!!!!!!!!!!!!!!!

Quote:

---

Originally Posted by szlamany

First thing you must do is CONNECT to the ACTUAL MACHINE that is hosting the SERVICE. Get on the BOX - use REMOTE DESKTOP - then make a connection with SSMS.

#1 - have you done that yet?

---

I have connected to the server using RDS and run MS SQL Server Management Studio Express on server and it connects immediately in both modes

Quote:

---

Originally Posted by szlamany

#2 - can you do that?

---

Yes I can do that and I have done that already.

My software when run on server also connects immediately.

TIA

Yogi Yang

Ok - now we are talking.

I've attached a screen shot of which I would like you to duplicate. Connect with SSMS on the server - as you said you can - and RIGHT-CLICK the SERVER in the OBJECT EXPLORER pane on the LEFT - choose PROPERTIES.

Select SECURITY first - make a screen shot - attach that.

Select CONNECTIONS second - make a screen shot - attach that.

Click the VIEW CONNECTION PROPERTIES link (lower left) - make a screen shot - attach that.

I've done that here myself and I'm attaching same for you to see exactly what I mean.

Sorry for the delay. I had to go out of office for official business.

Here are the screen shots as requested by szlamany.

Attachment 105229

Attachment 105231

Attachment 105233

Thanks for helping me out on this issue.

Regards,

Yogi Yang

Ok - post#16 has a different MACHINE NAME - FCC5011292\SQLEXPRESS - and post #22 clearly shows the actual MACHINE name as OEM-6BSILIDA2O7\SQLEXPRESS.

Why would that be?

Is this an actual DOMAIN?

I cannot believe we are 23 posts and 8 days into something that should be fixable in 2 minutes - wow - this must be frustrating for you!

Quote:

---

Originally Posted by szlamany

Ok - post#16 has a different MACHINE NAME - FCC5011292\SQLEXPRESS - and post #22 clearly shows the actual MACHINE name as OEM-6BSILIDA2O7\SQLEXPRESS.

Why would that be?

Is this an actual DOMAIN?

I cannot believe we are 23 posts and 8 days into something that should be fixable in 2 minutes - wow - this must be frustrating for you!

---

The machine name change is because the EDP guy of my client just replaced the old server with a new one with a hope that my software will work properly in new configuration. But machine name change is not an acute issue. I am very fustrated because my software cannot connect to SQL server while another software can!! :confused:

I have high hopes that you will be able to help me solve my problem once and for all.

Regards,

Yogi Yang

Ok - now open the OBJECT EXPLORER again - find the DB in question - right-click and get PROPERTIES.

Now - under OPTIONS - under the STATE (way at the bottom) - FALSE/NORMAL and MULTI_USER should be the values - are they?

Now - under PERMISSIONS - give us a screen shot.

Now - under MIRRORING - give us a screen shot.

Finally - back to PERMISSIONS - click on VIEW SERVER PERMISSIONS - brings you back to the other page you were on a couple of days ago. Anything checked in the EXPLICIT area?