2

I am a developer-trying-to-play-admin and wish to connect to a remote sql server from my development machine using a sql server user ("op_web").

When I try to connect from vs2008 Server Explorer, I can connect to the server, but no databases are listed. If I connect using the server admin user, all databases are listed as expected.

The server is a relatively fresh install made by me.

I have

  • allowed for remote connections in sql server.

  • created the login op_web at server level

  • created a user at database level and assigned to login with same name

  • assigned roles to the user to allow for reading and writing - I have assigned no schemas and default schema for the user is dbo.

If I log on (locally at server) using sqlserver management studio/sqlserver authentication and the created login, I can display and alter table data as I would expect.

Remote access gives me no choice of databases.

In Visual Studio on the client machine, I get the server name listed in the dropdownlist with discovered sql server - and as mentioned, I am able to connect using windows user (adminstrator account). Furthermore I've tried from another physical machine with same negative results. It doesn't smell like a firewall issue, but I've tried to disable the server firewall just in case, but that didn't fix it either. I have another database server where everything works, and I've cloned every setting user-to-user (So it smells like the problem is related to the sql server instance rather than the user).

Any pointers to what I might have missed?

(This question was asked on https://stackoverflow.com/questions/1386223/how-to-allow-access-for-a-sql-server-user)

3 Answers 3

2

SQL Server "users" have two parts to them.

  1. The server login. Defined as server level, and maybe associated with server roles (e.g. dbcreator: "Members of the dbcreator fixed server role can create, alter, drop, and restore any database"). Map Windows users to server roles (some of this is done automatically, but depends on the version of SQL Server1)

  2. Database users. These are created by database, given database roles (e.g. "dbo" for full control ("database owner") or "dbreader" to be able to read data). When create these are associated with a server login.

You can define server and database roles to give specific permissions).

See SQL Server Books Online (or on MSDN) for more details.

So you need to, using the administrator account, create a server login for your Windows user account, and give it access to the databases you need to work with. If you are defining things (tables, views, stored procs, ...) you really need "dbo" role, or quite some work for finer grained control.

Remember to test the application with just the access that application needs (e.g. it is a rare application that would create/drop/alter views or tables).


1 In SQL Server 2005 the host machine's local Administrators group is mapped to a login with "sysadmin" server role, in 2008 you specify which accounts are to be so mapped in the setup).

3
  • Hi Richard, Thanks for taking the time! I have done just that except I have enabled Mixed Mode authentication and wish to use a sqlserver user for the access. I have setup the login and user as well, but for some reason I can still only log into the server with the administrator login. Is there a place I should look for logging that could reveal on what basis I am denied access? Or a service that is needed for sql users but not domain users? Sep 7, 2009 at 10:20
  • I found the following in the windows\application log: The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies. Sep 7, 2009 at 10:20
  • I get event below in the security log. It could look like it's trying my workstation login and not the user/pw that I supply when trying? An account failed to log on. Subject: Security ID: NULL SID Account Name: - Account Domain: - Logon ID: 0x0 Logon Type: 3 Account For Which Logon Failed: Security ID: NULL SID Account Name: Anders Account Domain: pc2009b Failure Information: Failure Reason: Unknown user name or bad password. Status: 0xc000006d Sub Status: 0xc0000064 Process Information: Caller Process ID: 0x0 Caller Process Name: - Sep 7, 2009 at 10:55
1

To be able to be seen on the network (connect remotely), you'll need to verify that the SQL Server instance has a network library like TCP/IP configured for it. You can configure this using SQL Server Configuration Manager. If you had to make a change, it won't take effect until you restart SQL Server. Now, if it's a named instance, you'll also want to ensure the SQL Server Browser service is running. That provides the TCP port to a client trying to find it with regards to a named instance. And you'll need to make sure there are exceptions on your firewall for both services.

With respect to your SQL Server login, if you try to login using SQL Server Management Studio, if you get an error indicating it's not a trusted connection/account, you're SQL Server is configured to use Windows only authentication, which means you can't connect using a SQL Server based login. In your case you've indicated you've set it for Mixed Mode, which is what you want. If, however, you didn't restart SQL Server after making the change, that could be the issue. Again, this is another one of those settings only run at start-up.

The Kerberos error you can disregard. It's because only a domain admin or the computer account itself (if you're running as Nework Service on Windows 2003) can register an SPN. So if you're using anything else, that error shows up. In that case it's best to create the SPN manually but that shouldn't enter in here because that only shows up for Windows authentication. Kerberos has no impact on SQL Server based logins.

Now with respect to the SQL Server user, are you able to connect to the SQL Server with it locally using SQL Server Management Studio?

0

Apologies are in order - problem is solved now:

I reinstalled the entire server and added sql server as first thing - I had a suspicion that VisualSVN, working via SSL, was playing havoc with a secure connection also needed by sqlserver login.

In fact the problem was still there but trying again to disable the firewall actually solved the immediate problem.

I punched a hole in the firewall for 1433/TCP and things are where I want them.

Thanks for your time, though...

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .