search

appsmithorg / appsmith

Platform to build admin panels, internal tools, and dashboards. Integrates with 25+ databases and any API.
https://www.appsmith.com
Apache License 2.0
34.51k stars 3.72k forks source link

[Feature]-[48]:MsSQL plugin - Enable connection with named instance / unable to connect with named instance #15531

Open sumitsum opened 2 years ago

sumitsum commented 2 years ago

Is there an existing issue for this?

Description

A user reported that they are not able to connect with a named instance. A named instance looks like this: my_ip\name. Ref: https://discord.com/channels/725602949748752515/760761686549463060/1002178664433197140

sorry, I can't share this environment, but it's quite easy to install a named instance of sql server
during the installation you can choose if you want a default instance, and the name will be the name or ip of the machine
or otherwise a named instance, in the form of hostname\instancename
it's this second options that I cannot make it work

Steps To Reproduce

  1. Create a named MsSQL server
  2. Try to connect and run query on the server.

Workaround

Named instances use dynamic port. A workaround is to use fixed port number by making the MsSQL server listen on a static port, then this port number can be used instead of the instance name. Ref: http://woshub.com/change-default-port-number-tcp-1433-ms-sql-server-instance/

Note

Hi, if you are interested in the fix for this issue and if you are willing to test the fix provided via a custom docker Appsmith container with the fix (you can self host this custom instance), please let me know by writing a comment on this issue and tagging @sumitsum in the comment.

Public Sample App

No response

Version

self hosted

Front logo Front conversations

mourantDavidSearle commented 2 years ago

Is there an existing issue for this?

  • [x] I have searched the existing issues

Description

A user reported that they are not able to connect with a named instance. A named instance looks like this: my_ip\name. Ref: https://discord.com/channels/725602949748752515/760761686549463060/1002178664433197140

sorry, I can't share this environment, but it's quite easy to install a named instance of sql server
during the installation you can choose if you want a default instance, and the name will be the name or ip of the machine
or otherwise a named instance, in the form of hostname\instancename
it's this second options that I cannot make it work

Steps To Reproduce

  1. Create a named MsSQL server
  2. Try to connect and run query on the server.

Public Sample App

No response

Version

self hosted

@Nikhil-Nandagopal - I am just starting a trial of appsmith_ for our organisation and have come accross this issue immediately. We use nothing but MS SQL named instances in our company and so not being able to connect to them halts any advancement we can make with the platform. Please can you address this soon? I was really encouraged by this product but this is a significant issue for me :( I'm in touch with support to find out if a workaround is possible, I really hope it is!

sumitsum commented 2 years ago

@mourantDavidSearle very sorry that we couldn't fix this issue earlier. A workaround does not seem possible right now. We will be prioritizing this issue and likely fixing it within the next 2 weeks.

mourantDavidSearle commented 2 years ago

Thanks @sumitsum - I managed to get my own workaround by hardcoding the port used by the MS SQL named instance. This at least meant I was able to begin bring data back. Look forward to trying the enhancement in a couple of weeks!

sumitsum commented 2 years ago

@mourantDavidSearle I have been unable to successfully set up a named MSSQL instance on a windows machine that is remotely accessible. Would it be possible for you to share a test credentials on sumit@appsmith.com ?

mourantDavidSearle commented 2 years ago

@sumitsum - Sadly my work would not permit you to connect through our firewall. You can setup a named instance of SQL using MS SQL Express on any Windows machine but if you can't gain access to one that its going to be difficult! Sorry, would help more if I could. Maybe if you had an Azure or AWS Windows box you could use that?

sumitsum commented 2 years ago

@mourantDavidSearle thanks a lot for the info :) . I will try again with MS SQL Express.

mourantDavidSearle commented 2 years ago

image

Usually it defaults to the localhost\SQLExpress and you can connect to the named instance that way. You also need to make sure the SQL Browser service is running too - but that should be enabled by default (see SQL Server Configuration Manager).

image

Finally, make sure firewall is open and allows connections (Windows firewall can get in the way sometimes, espeically if you're connecting from another box).

sumitsum commented 2 years ago

Thanks a lot for the detailed steps @mourantDavidSearle. I will try them out.

sumitsum commented 1 year ago

MsSQL 6m usage: 12 % of users impacted: 50

Stats

Stat Values
Reach 6
Effort (months) 0.25
FlexyMax commented 1 year ago

@mourantDavidSearle Hi David, I am trying to connect to a SQL server instance too. Please, can you share your workaround or if you found another way to connect the instance?

Thanks @sumitsum - I managed to get my own workaround by hardcoding the port used by the MS SQL named instance. This at least meant I was able to begin bring data back. Look forward to trying the enhancement in a couple of weeks!

mourantDavidSearle commented 1 year ago

Hi, Yes, you need to discover the port the SQL Server Instance is running on. See this article which gives you a few ways: https://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/ Once you have done that, you can use the port number in the AppSmith connection setup – just put the server name (not the full instance name), and then the port. The port could change each time SQL Server is restarted however I've found them to be pretty static generally. I thought they'd have resolved this defect/feature by now… Thanks, Dave

sumitsum commented 1 year ago

Hi, if you are interested in the fix for this issue and if you are willing to test the fix provided via a custom docker Appsmith container with the fix (you can self host this custom instance), please let me know by writing a comment on this issue and tagging me in the comment.

FlexyMax commented 1 year ago

Hello David,

I hope this email finds you doing well. I wanted to express my gratitude for your response and for providing me with a solution that works flawlessly. I have been testing your suggested work-around and it has been working perfectly fine. For the time being, I will continue using it until we find another solution or until I switch to a default instance in the future.

Once again, I appreciate your help and I would like to extend my assistance to you for any queries or issues you may face with SQL or Appsmith. Please feel free to reach out to me whenever you need help.

Thank you,

Carlos

De: David Searle @.> Enviado: martes, 11 de abril de 2023 4:35 Para: appsmithorg/appsmith @.> Cc: Carlos A. Miranda @.>; Comment @.> Asunto: Re: [appsmithorg/appsmith] [Bug]-[48]:MsSQL plugin (Issue #15531)

Hi, Yes, you need to discover the port the SQL Server Instance is running on. See this article which gives you a few ways: https://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/ Once you have done that, you can use the port number in the AppSmith connection setup – just put the server name (not the full instance name), and then the port. The port could change each time SQL Server is restarted however I've found them to be pretty static generally. I thought they'd have resolved this defect/feature by now… Thanks, Dave

— Reply to this email directly, view it on GitHubhttps://github.com/appsmithorg/appsmith/issues/15531#issuecomment-1502912241, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2VVU7F2PJMDARSY42AEOFLXAUJTXANCNFSM5473SX5Q. You are receiving this because you commented.Message ID: @.***>

daaliachhak17 commented 3 months ago

Is anyone working on this? or is this closed?