March 4, 2009 at 9:59 am
Hi,
I've been battling this all day. I've looked all over Google, to no avail. I would be SO SO grateful if someone can offer me some advice here.
I'm working on a customer's install of SQL Server 2005. I've logged into Microsoft SQL Server management studio using a Windows account. I've created a database, created a SQL user under the SQL Server with the following effective permissions:
CONNECT SQL
CREATE ANY DATABASE
VIEW ANY DATABASE
I've set the SQL Server to allow remote connections (max connections = 0 (unlimited)), and to allow for SQL account logins as well as windows account logins.
I've added the user to the database (default schema: dbo), given them the following effective permissions:
CONNECT
CREATE TABLE
CREATE VIEW
DELETE
EXECUTE
INSERT
SELECT
UPDATE
I CAN:
Connect to the SQL Server using my SQL account. View the databases and successfully execute the following command:
USE [MYDATABASE]
where [MYDATABASE] is the database I created with the user added under it.
I CANNOT:
Connect using Microsoft SQL Server 2005 JDBC Driver. I keep getting the following error in the SQL Server logs:
Login Failed for user [MYUSER];
Error 18456, Severity: 14, State: 12
Note: When I add the user into the sysadmin server role, this error goes away, but I can only have 1 connection at a time, which is no good (I'm guessing because of the whole single admin account connection rule).
My user has server roles: public, and dbcreator
What on earth have I done wrong?! :crying:
Please, please help!
March 4, 2009 at 11:57 am
The error state 12 indicates that your sql account has no access to the server, have you grant login to it? Can you also try connecting over named pipe, what happens?
-Roy
March 4, 2009 at 11:59 am
I got this from another site. This might help you in the future as well...
ERROR STATE
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required
-Roy
March 4, 2009 at 3:49 pm
nathanielt (3/4/2009)
Hi,Note: When I add the user into the sysadmin server role, this error goes away, but I can only have 1 connection at a time, which is no good (I'm guessing because of the whole single admin account connection rule).
I am a bit confused about that comment? What you mean only one sysadmin connection is allowed? Did you set the database to restrictive mode or single-user mode?
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 5, 2009 at 1:20 am
SQL Server 2005 allows only one admin account, so I'm aware.
I got those other comments from Google, too, but as I created the login on the server and then associated it with a user on the database, I don't see how they apply...can you elaborate?
Thanks for your help.
March 5, 2009 at 2:52 am
Turns out the installer hadn't enabled TCP/IP connections. D'oh!
March 5, 2009 at 9:16 am
SQL Server allows more then one admin connection; think about it in team of multiple DBA supporting large systems if only one admin connection was allowed / instance it would make it difficult.
I think what you might be confusing it with is DAC (Dedeicated Admin Connection); this is an emergency connection which only allows one connection to trouble shoot issues.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 5, 2009 at 10:49 am
Mohit (3/5/2009)
SQL Server allows more then one admin connection; think about it in team of multiple DBA supporting large systems if only one admin connection was allowed / instance it would make it difficult.I think what you might be confusing it with is DAC (Dedeicated Admin Connection); this is an emergency connection which only allows one connection to trouble shoot issues.
Thanks.
Let me piggyback on this one. Admin access is not restricted except with respect to the DAC. And as Mohit indicates, it's only there for emergency access (such as when you have a runaway query and cannot login via the normal connection means).
K. Brian Kelley
@kbriankelley
February 15, 2010 at 4:15 am
I also have a similar kind of problem, http://www.sqlservercentral.com/Forums/Topic865240-146-1.aspx#bm865288
my super users are not able to connect to SQL via Windows login untill i give them sysadmin 🙁
February 16, 2010 at 1:11 am
had the same problem, was a problem with Kerberos SPN for SQL server was missing
February 17, 2010 at 12:00 am
But i am using windows authentication not Kerberos.
but it works if i give user sysadmin but in that case the user wil be admin which i dont want.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply