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….
- 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….
- 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.
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>;