Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
Well, I did the "telnet <machine> 1433" from remote machine and that works, and in tcpview, I can see sqlservr.exe has 1433 and port 1434 and looks like two sqlbrowser.exe processes are connected to port 1434 for some reason and listening on 2382. BUT my JDBC stuff still gets login Denied :(. –  Dean Hiller Aug 18 '12 at 15:07
    
oh, and I did as you said and completely recreated the database with sa and created the user with sa and then made secure1 the db_owner. –  Dean Hiller Aug 18 '12 at 15:07
    
See the update section in the answer please. Notice I do NOT use a port number. –  Brent Pabst Aug 18 '12 at 15:15
    
GREAT!!! worked like a charm, thanks!!!! –  Dean Hiller Aug 18 '12 at 15:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.