Re: SQL Server 2005 Login Problem

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: Windows Authentication in asp.net 2005 to SQL Server?
    ... If the domains do not trust each other, Windows authentication is not going ... Basic authentication sometimes makes the need for Kerberos delegation go ... generic account to do the backend data stuff on our SQL Server. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Remote connection failed
    ... If you are going over a remote connection or are not logging ... into the domain where SQL Server is then no, Windows ... authentication won't work. ...
    (microsoft.public.sqlserver.connect)
  • Re: Changing passwords / Blocking SA login attempts
    ... I went into the Server Config properties and changed to Windows ... My login for Start and Run SQL server was in this format. ... I am supposing this was an SQL authentication, ...
    (microsoft.public.sqlserver.security)
  • Windows Authentication with IIS on separate machines
    ... Yes, setting Basic Authentication in IIS works, but the ... >in SQL server but doesn't work if user account was ... >imported from a Windows account. ...
    (microsoft.public.sqlserver.security)
  • Re: Login failed for ServerGuest
    ... I think it is not a limitation in Windows 2000. ... access SQL server on Win2000 server by using Windows authenctication if I ... | I have noticed that when I try to log in using Windows Authentication ...
    (microsoft.public.sqlserver.connect)