Windows – MS SQL can’t connect with JDBC…permissions issues

sql serverwindows

I used "sa" to create a user "secure1" and I can connect as secure1 using Management Studio locally and remotely.

When I then switch to JDBC with the url(I use two \ in the url below but stackexchange stripped the other slash out because java sees the \ as an escape sequence)

jdbc:sqlserver://192.168.111.111\MYDBQA:1433;Initial Catalog=mydatabase

I just get a Login Denied. If I erase Initial Catalog, I get Login Denied. I ended up giving my secure1 user the sysadmin server role in addition to the public he already had.

Currently, On User Mappings for the 'mydatabase' I gave him every permission except db_deny**. I also added him to master, model, msdb, tempdb and marked every permission except the db_den*** ones though this is not working either.

My more important question first so I can get something working first….

  1. How to get this to work or is there a document on setting up MS SQL for a JDBC connection somewhere?

Then to make it a bit better….

  1. How to properly set it up so my user can read/write/create/update all tables in 'mydatabase' but not do anything else in any other databases.

NOTE: I CAN connect with the "sa" user over JDBC just fine.

thanks,
Dean

Best Answer

Dean,

A couple of things.

  1. Undo all of the permission changes you made to the other databases, none of them were required. Better yet delete the user you created which should in turn delete all of the other permission changes.
  2. Re-create your user, don't give them any server roles just create the user.
  3. In the User Mappings section assign your myDatabase to it and grant the db_owner right. (Full Control over just this database)
  4. Make sure you have enabled the TCP/IP protocol in the SQL Server Configuration tool.

Try it now.

Update:

Your connection string is wrong. It appears you are using a named instance of SQL Server and the named instance does not use the default port number. Its good that you have the SQL Browser service running but your connection string should look more like this:

jdbc:sqlserver://serverName;instanceName=instance1;integratedSecurity=true;<more properties as required>;

Related Topic