Re: Newbie question on SQL connection

From: Kevin Spencer (kevin_at_DIESPAMMERSDIEtakempis.com)
Date: 01/19/05 

Date: Wed, 19 Jan 2005 09:58:38 -0500

Hi Rudy,

Let me educate you about SQL Server security. SQL Server has 2 security
configurations, as you've discovered. One is "Windows Only" and the other is
"Mixed." The "Trusted Connection" error is associated with the "Windows
Only" security mode. In the "Windows Only" security mode, only valid domain
accounts that are specifically granted certain types of permissions in the
SQL
Server have access. Note that there are 2 conditions: First, a Windows
domain user account. Second, granting permissions to these accounts in SQL
Server. The second security model, "Mixed," means that SQL Server can
maintain its own user accounts, which are separate from domain user
accounts. These are SQL Server only. Note that "Mixed" means that you can
have both Windows domain user accounts and SQL Server user accounts in SQL
Server. In addition to creating these accounts, whether domain or SQL Server
accounts, you must again grant SQL Server permissions to the accounts.

In other words, regardless of whether you use "Windows Only" or "Mixed," you
need to specifically grant the accounts the permissions they need to access
SQL Server objects. Simply logging in with a domain user account doesn't
allow the account to access anything, any more than creating a domain user
account doesn't automatically grant any domain privileges to that account.

In conclusion:

"Windows Only": Log in with domain account that has been granted explicit
database permissions.
"Mixed": Log in with either domain account or SQL Server account that has
been granted explicit database permissions. 

-- 
HTH,
Kevin Spencer
Microsoft MVP
.Net Developer
Neither a follower nor a lender be.
"Rudy" <Rudy@discussions.microsoft.com> wrote in message 
news:F5CE656C-B35B-44BF-90A7-3A8F57627A3E@microsoft.com...
> Hello all!
>
> Well, I hate to beat a dead horse, but I'm going to ride this dead thing
> till I get it.  I really appreciate everyones input. If anybody wanted to
> make a trip to Wisconsin, there would be a case of your favorite brew. LOL
> OK, So I fixed my web config, took out the Identity Impersonate, and fixed
> some simple syntax errors. Now when I use this code
> ''' Protected Const SQL_CONNECTION_STRING As String = _
>    '''   "Persist Security Info=False; Server=sbserver;" & _
>    '''  "Trusted_Connection=Yes; DataBase=IMSmidwest;" & _
>    '''     "Integrated Security=SSPI"
> I get Login failed for user 'STMBL\ASPNET'. Reason: Not associated with a
> trusted SQL Server connection.
>
> With this code
> Protected Const SQL_CONNECTION_STRING As String = _
> "Data Source=192.168.100.120,1533;Network Library=dbmssocn;Initial
> Catalog=IMSmidwest;User ID=ASPNET;Password=******;"
>
> I get Login failed for user 'STMBL\ASPNET'.
>
> One thing I have noticed is when I run SQL query Analizer, I can't login
> with SQL sever authentication, but I can with windows.  Now I have my 
> server
> setup windows Only, I tried  Windows and SQL server.  It didn't make a
> diffrence. I'm so deep into this, I'm not even sure what I'm asking 
> anymore.
> I noticed that ASPNET was made for me as a user on my develper computer, 
> but
> on my domain I had to make the user myself. But it's still a diffrent 
> user,
> "localhost\aspnet vs STMBL\aspnet.
> I can still can connect to my server in SQL manager, and I can work with
> both SQL servers.  I have a feeling that I'm trying so many diffrent 
> things,
> that the combination of settings is wrong, just not sure which is the best
> way to approach this. Any other thoughts on this?
>
> Thanks for your time!
>
> Rudy

Relevant Pages

  • Re: Cannot open database requested in login
    ... Assuming your goal is to use windows integrated ... security then leave out the username ... ASP.NET service) as a login to SQL Server and with access to the ... >> you should see security tab, change authentication to "SQL ...
    (microsoft.public.sqlserver.security)
  • Re: Reason: Not associated with a trusted SQL Server connection.
    ... but does that mean ASP uses the IUSR account to access the SQL ... > There are two sets of authentications: Windows, and SQL Server. ... If using integrated security, ...
    (microsoft.public.inetserver.asp.db)
  • ASP.NET "Custom" Security
    ... SQL Server, DB2, MySQL, Oracle, ... Windows server at the other end. ... to be authenticated by the stored procedure against the ... Is this a reasonable security model? ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Is there any way to prevent hacker trying to guess sa password?
    ... the Space Shuttle has some very strong windows -- get my point. ... doubt THE single most significant security flaw -- this is the green light ... spoofing was known about when the protocol was introduced and Microsoft did ... > need to use some other mechanism to connect to SQL Server. ...
    (microsoft.public.sqlserver.security)
  • Re: SMS reinstall
    ... security of the SQL server? ... check the logins and security. ... From there make sure all SMS_Service accounts ... >>> We are having an issue with SMS 2.0. ...
    (microsoft.public.sms.setup)