Results 1 to 25 of 25

Thread: Cannot connect to MS SQL Server

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    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

  2. #2
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Cannot connect to MS SQL Server

    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;

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Cannot connect to MS SQL Server

    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?

  4. #4
    I'm about to be a PowerPoster! Joacim Andersson's Avatar
    Join Date
    Jan 1999
    Location
    Sweden
    Posts
    14,649

    Re: Cannot connect to MS SQL Server

    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.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: Cannot connect to MS SQL Server

    Quote Originally Posted by Joacim Andersson View Post
    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.

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: Cannot connect to MS SQL Server

    Quote Originally Posted by jmcilhinney View Post
    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

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Cannot connect to MS SQL Server

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: Cannot connect to MS SQL Server

    Quote Originally Posted by techgnome View Post
    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

  9. #9
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    Re: Cannot connect to MS SQL Server

    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

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Cannot connect to MS SQL Server

    Quote Originally Posted by yogiyang View Post
    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 don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: Cannot connect to MS SQL Server

    I checked that out also.

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

    Regards,

    Yogi Yang

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Cannot connect to MS SQL Server

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: Cannot connect to MS SQL Server

    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

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Cannot connect to MS SQL Server

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Cannot connect to MS SQL Server

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

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: Cannot connect to MS SQL Server

    Error I get in MS SQL Server Management Studio Express
    Name:  100 Connection Error -1.JPG
Views: 412
Size:  21.8 KB

    The error I get from my software
    Name:  102 error.JPG
Views: 333
Size:  12.4 KB

    Please help.

    TIA

    Yogi Yang

  17. #17
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Cannot connect to MS SQL Server

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: Cannot connect to MS SQL Server

    Quote Originally Posted by szlamany View Post
    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

  19. #19
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Cannot connect to MS SQL Server

    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!!!!!!!!!!!!!!!!!!!!!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  20. #20

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: Cannot connect to MS SQL Server

    Quote Originally Posted by szlamany View Post
    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 View Post
    #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
    Last edited by yogiyang; Sep 25th, 2013 at 01:22 AM.

  21. #21
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Cannot connect to MS SQL Server

    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.
    Attached Images Attached Images  

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: Cannot connect to MS SQL Server

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

    Here are the screen shots as requested by szlamany.

    Name:  1_Security.JPG
Views: 343
Size:  52.2 KB

    Name:  2_Connections.JPG
Views: 352
Size:  59.3 KB

    Name:  3_Connection Properties.JPG
Views: 355
Size:  146.2 KB

    Thanks for helping me out on this issue.

    Regards,

    Yogi Yang

  23. #23
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Cannot connect to MS SQL Server

    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  24. #24

    Thread Starter
    Addicted Member
    Join Date
    Mar 2007
    Location
    India
    Posts
    227

    Re: Cannot connect to MS SQL Server

    Quote Originally Posted by szlamany View Post
    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!!

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

    Regards,

    Yogi Yang

  25. #25
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Cannot connect to MS SQL Server

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width