SQLExpress

Feature requests and their debate

SQLExpress

Postby exils » Wed Jun 06, 2012 3:51 pm

Any chance support could be added for working with SQLExpress databases.

Thanks.
exils
 
Posts: 1
Joined: Wed Jun 06, 2012 3:50 pm

Re: SQLExpress

Postby Serge » Wed Jun 06, 2012 5:37 pm

Hi!

Do you mean MS SQL Server Express or SQLExpress ODBC layer for xBase? Or may some other SQLExpress (actually this name used by many very different products).

MS SQL Express supported with standard MS SQL drivers. But you need to enable TCP/IP connectivity on server. Check this articles: http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/4e8a9c16-9a41-486d-a57a-78b90d7ccceb, http://www.mombu.com/microsoft/sql-server-jdbc-driver/t-using-jdbc-for-sqlexpress-248826.html.

SQLExpress for xBase provides ODBC access so you can use plain ODBC driver.
Serge
 
Posts: 1526
Joined: Sat Feb 26, 2011 8:24 pm
Location: SPb

Re: SQLExpress

Postby spenico » Fri May 01, 2015 6:31 pm

You can connect to Microsoft SQLEXPRESS using DBeaver, but you need to change a few settings. I've done this with a SQL Server 2014 SQLEXPRESS database using a Trusted Connection.

Most of this just repeats what is documented by the posts and links on this site, but there are a couple of tweaks. Also, it's updated for the 2014 version of SQLEXPRESS.

In general terms

  1. Make changes to SQLEXPRESS itself using the Sql Server Configuration Manager tool
    1. Enable TCP/IP for SQLEXPRESS
    2. For SQLEXPRESS TCP/IP, clear out the dynamic port and hardcode 1433
  2. When creating the connection in DBeaver, enter/tweak a parameter or two on the "Driver properties" tab.
In more detail

The first two items, above (1a and 1b) are set in the Sql Server Configuration Manager tool.
  • In that tool, go to:
    SQL Server Network Configuration | Protocols for SQLEXPRESS | TCP/IP
  • Enable the TCP/IP protocol in the Properties dialog for TCP/IP. (You can also enable it on the right-click menu.)
  • Set the TCP/IP port -- which can only be done in the Properties dialog (i.e., can't do it with just a right-click).
    On the "IP Addresses" tab of that Properties dialog, I used the IPAll settings at the bottom. Clear out TCP Dynamic Ports, then set TCP Port to 1433.
After making these changes, I was able to use DBeaver to create either a jTDS or a "Microsoft Driver" connection for my 2014 SQLEXPRESS DB with Trusted Connection.

To create a jTDS connection in DBeaver
  1. Create a new connection, specifying MS SQL Server | jTDS driver
  2. Fill out the info on the first (General) tab, complete with User name and Password.
    I was able to enter my User name as just my Windows user name, not domain\username. But domain\username worked too, and that may be required on some networks. (I'm not sure.)
  3. Go to the "Driver properties" tab and re-specify the DOMAIN and the USER as two separate parameters.
    I did not need to re-specify my PASSWORD on the "Driver properties" tab.
    And just to emphasize where to enter this: it's entered on the "Driver properties" tab -- not in the dialog box you get to by clicking the "Edit Driver Settings" button that's on the General tab.
  4. Click the "Test Connection..." button to make sure it works, click Next a couple of times, then click Finish.
To create a "Microsoft Driver" connection in DBeaver
  1. Create a new connection, specifying MS SQL Server | Microsoft Driver
  2. Fill out the info on the first (General) tab, without specifying User name and Password (leave them blank).
  3. Go to the Driver properties tab and set integratedSecurity=true.
    And, once again, just to emphasize where to enter this: it's entered on the "Driver properties" tab -- not in the dialog box you get to by clicking the "Edit Driver Settings" button that's on the General tab.
  4. Click the "Test Connection..." button to make sure it works, click Next a couple of times, then click Finish.
Which means, incidentally, that the "Microsoft Driver" connection is better than the jTDS connection because there's no need to change your password in the connection configuration -- and no risk that your domain account will get locked because you've forgotten that you have an out-of-date password.
spenico
 
Posts: 1
Joined: Fri May 01, 2015 5:04 pm

Re: SQLExpress

Postby Serge » Sat May 02, 2015 7:30 pm

Wow, thank for great SQL Server How-To!
Serge
 
Posts: 1526
Joined: Sat Feb 26, 2011 8:24 pm
Location: SPb

Re: SQLExpress

Postby sdrzm » Mon May 25, 2015 7:32 pm

Thanks, great job!
sdrzm
 
Posts: 10
Joined: Wed Jan 07, 2015 9:58 am


Return to Feature Requests



Who is online

Users browsing this forum: Google [Bot] and 3 guests