Re: SQL Server 2005 Login Problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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: ASP.NET User.Identity.Name value after a domain username chang
    ... Since .NET is just calling into Windows API calls to resolve the user name, ... I have an ASP.NET application that is on a Win2000 server in a domain ... I have a very confusing issue when the domain login of a user is ... I thought this might have been some caching issue on the client so I ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: CAN WE LOGIN TO A WINDOWS 2003 ACTIVE DIRECTORY DOMAIN OVER TH
    ... Having a user login to a windows 2003 server over the internet is not a good ...
    (microsoft.public.windows.server.active_directory)
  • Re: SQL Server 2005 Login Problem
    ... There are two authentication method in SQL Server. ... 1- Windows Authentication ... 2- Mixed Authentication (through SQL Server logins and Windows accounts) ...
    (microsoft.public.sqlserver.clients)
  • mapped logins only work when defaulted (and windows auth registered)
    ... Mapped logins do not work for my Oracle linked server, ... SQL Server Enterprise Edition, 8.0.844 ... I have two logins, a Windows login and a SQL Server login. ...
    (microsoft.public.sqlserver.programming)
  • RE: My Documents Redirection error
    ... We can upgrade the instance SharePoint ... >> is no need to manually migrate data from MSDE to SQL server and all dada ... >> How to back up and restore http://companyweb data in Windows Small ...
    (microsoft.public.windows.server.sbs)