Re: SQL Server 2005 Login Problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

This is workgroup SQL 2005. Here's the odd part of this... a machine that
IS NOT a domain member can connect without issue. Domain machines cannot.
I've defined the users that cannot connect in SQL and given them rights to
the database, but no go. Can't connect using sa account either. Also
cannot setup an ODBC connection.

A little background on this...

Customer had an existing 2000 domain. SQL 2005 was setup on a new server
that they installed 2003 SBS on. Obviously this is a different domain and a
trust cannot be established with the old domain. We joined one of the
problem machines to the new domain to alleviate any issue with the trust
aspect, but this didn't help.



"Ekrem Önsoy" <ekrem@xxxxxxxxxxxx> wrote in message
news:2762B0B7-21E0-425C-ADEC-2BEA0D4A2707@xxxxxxxxxxxxxxxx
Is that SQL Server 2005' s Workgroup Edition? As I don't have Workgroup
Edition on my test environment I don't know its default values.

However there are something must be turned on before a remote connection
to SQL Server. For example in Express and Developer Editions of SQL
Server, Remote Connections are disabled by default. You can enable this
option using SQL Server Surface Area Configuration tool.

And then configure your TCP protocol from the SQL Server Configuration
Manager.

--
Ekrem Önsoy



"Absolutely" <nospam@xxxxxxxxxxx> wrote in message
news:uPQO4oyQIHA.1212@xxxxxxxxxxxxxxxxxxxxxxx
I'm getting a similar issue. SBS with SQL 2005. XP Pro SP2 machines
just won't connect to it. SQL server is in mixed mode. We're using the
sa login at the workstations and it still won't connect.





"Andy Baker" <abaker@xxxxxxxxxxxxxxxxxx> wrote in message
news:13j8hg7l3m460d5@xxxxxxxxxxxxxxxxxxxxx
Hello Ekrem

Thanks very much for the advice, I have got it working now. It is only a
simple 3 station network and all users only require access to a single
SQL server database, so I created a login for the users group and only
allowed access to the one database. I think it is OK - I can get a
connection now! Thanks again.

Andy Baker

"Ekrem Önsoy" <ekrem@xxxxxxxxxxxx> wrote in message
news:93A243FE-4994-4224-9A7F-210D2C1616DA@xxxxxxxxxxxxxxxx
Hello Andy,

There are two authentication method in SQL Server.
1- Windows Authentication
2- Mixed Authentication (through SQL Server logins and Windows
accounts)

When you use Windows Authentication, yes, as you mentioned you do not
need usernames and passwords. Instead, you need an authenticated
Windows account. However, you have to create Login objects for those
Windows accounts. Otherwise, all Windows users would be able to reach
any content in databases right? Only creating Logins is not the
complete solution of course, then you'll need to map those users to the
appropriate databases and assign them roles and grant them permissons.
So, you'll ensure that every user has access to databases that you want
them to have.

In terms of managebility, it's better to create Windows Security Groups
and create logins for these groups when you use Windows authentication.
For example, you'd create a Windows group in your domain called
Accountants and add all users of accountance department to this group
and create a login in SQL Server for this group and grant to this group
the appropriate permissions. So, you'll accomplish your task in shorter
time. When you'd need to modify accounants' permissions, you'd perform
this only for Accountants Login (which is a Windows Security Group,
remember?) One stone, two birds, maybe more =)

When you use Mixed Authentication, you can use Windows accounts and SQL
Server logins both. However, this is not a recommended method for SQL
Server Authentication when possible.

--
Ekrem Önsoy



"Andy Baker" <abaker@xxxxxxxxxxxxxxxxxx> wrote in message
news:13j88fgquc8d8b4@xxxxxxxxxxxxxxxxxxxxx
Thanks for getting back to me. I don't really know much about how SQL
Server works, so please correct me if I have misunderstood you, but I
thought that the idea of Windows Authentication was that you didn't
need to use separate usernames / passwords, it worked when you were
logged into windows because it was a trusted connection (integrated
security =SSPI. My SQL Server 2000 installation doesn't have a
separate logon for each user - is 2005 different?. Do I have to set up
each user as a SQL Server user as well, and do I have to include
Userid and Password in the connection string? It is a default
installation.

Andy Baker

"Ekrem Önsoy" <ekrem@xxxxxxxxxxxx> wrote in message
news:D97FC7FA-9CD8-4C93-829F-29F35B3B9D5F@xxxxxxxxxxxxxxxx
Ensure you have approprate logins for those users who can't connect
in your SQL Server Logins, if not, create logins for those users.

Also, it's a default instance I believe, because you use only server
name in Data Source?

--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCDBA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT



"Andy Baker" <abaker@xxxxxxxxxxxxxxxxxx> wrote in message
news:13j6eo61mk6he1e@xxxxxxxxxxxxxxxxxxxxx
We have recently purchased Small Business Server Premium, with SQL
Server 2005 workgroup edition included. I has 3 XP PCs that I want
to access the SQL Server database, and have set it up with windows
integrated security. When I try to connect (from a VB.NET
application) I am getting an error 'Login failed for User' followed
by the domain name and user name. The connection string that I am
using is 'Data Source=MAIN; Database=Vanputer; integrated
security=SSPI; persist security info=False;" (where MAIN is the
server name and Vanputer is the database name) The application
connects fine to a server running SBS 2000 and SQL Server 2000, but
2005 is rejecting the login. What am I doing wrong. Thanks in
advance for any suggestions.

Andy Baker













.

Relevant Pages

  • Re: Permission question - another one
    ... If I add an Sql Login it does add the TRAVAC\ in front of the names, ... seems to be users that were setup to use SQL Server Authentication. ... RAPTOR is the Server that has SQL Server running on it. ... > " I could think I am taking permissions away from someone, ...
    (microsoft.public.sqlserver.programming)
  • Re: Renamed Windows login not found in SQL Server 2000
    ... It's almost like SQL tuck some knowledge away in an area ... of memory that only gets released on Windows stop. ... > I am running SQL Server 2000 SP2 with Windows ... > login gets corrupted) I am unable to add the new login to ...
    (microsoft.public.sqlserver.security)
  • Re: Login failed for user (null)
    ... Not associated with a trusted SQL Server connection. ... However, if you deal with the Domain controller, most of the controls there ... > (Windows 2003 server, domain member). ...
    (microsoft.public.inetserver.asp.db)
  • Re: Login failed for user (null)
    ... Not associated with a trusted SQL Server connection. ... However, if you deal with the Domain controller, most of the controls there ... > (Windows 2003 server, domain member). ...
    (microsoft.public.sqlserver.connect)
  • Re: Login failed for user (null)
    ... Not associated with a trusted SQL Server connection. ... However, if you deal with the Domain controller, most of the controls there ... > (Windows 2003 server, domain member). ...
    (microsoft.public.sqlserver.security)