Can't Switch to SQL Authentication (Mixed Mode)

We have a SQL installation that was setup with Windows Authentication.
The software we will run requires SQL Authentication (Mixed Mode).
When I right click on properties, go to the security tab, click the SQL
Authentication, click on OK and it exits. However, when I go back in
and look at the security tab, it is still set on Windows
Authentication. I tried restarting the SQL services and I get the same
thing.
Ideas?
I have resolved this issue by logging in as the domain admin. I had
previously tried using a local admin account.
jd
JohnDoe wrote:
> We have a SQL installation that was setup with Windows Authentication.
> The software we will run requires SQL Authentication (Mixed Mode).
> When I right click on properties, go to the security tab, click the SQL
> Authentication, click on OK and it exits. However, when I go back in
> and look at the security tab, it is still set on Windows
> Authentication. I tried restarting the SQL services and I get the same
> thing.
> Ideas?

Can't Switch to SQL Authentication (Mixed Mode)

We have a SQL installation that was setup with Windows Authentication.
The software we will run requires SQL Authentication (Mixed Mode).
When I right click on properties, go to the security tab, click the SQL
Authentication, click on OK and it exits. However, when I go back in
and look at the security tab, it is still set on Windows
Authentication. I tried restarting the SQL services and I get the same
thing.
Ideas?I have resolved this issue by logging in as the domain admin. I had
previously tried using a local admin account.
jd
JohnDoe wrote:
> We have a SQL installation that was setup with Windows Authentication.
> The software we will run requires SQL Authentication (Mixed Mode).
> When I right click on properties, go to the security tab, click the SQL
> Authentication, click on OK and it exits. However, when I go back in
> and look at the security tab, it is still set on Windows
> Authentication. I tried restarting the SQL services and I get the same
> thing.
> Ideas?

Can't Switch to SQL Authentication (Mixed Mode)

We have a SQL installation that was setup with Windows Authentication.
The software we will run requires SQL Authentication (Mixed Mode).
When I right click on properties, go to the security tab, click the SQL
Authentication, click on OK and it exits. However, when I go back in
and look at the security tab, it is still set on Windows
Authentication. I tried restarting the SQL services and I get the same
thing.
Ideas?I have resolved this issue by logging in as the domain admin. I had
previously tried using a local admin account.
jd
JohnDoe wrote:
> We have a SQL installation that was setup with Windows Authentication.
> The software we will run requires SQL Authentication (Mixed Mode).
> When I right click on properties, go to the security tab, click the SQL
> Authentication, click on OK and it exits. However, when I go back in
> and look at the security tab, it is still set on Windows
> Authentication. I tried restarting the SQL services and I get the same
> thing.
> Ideas?

Can't set the owner to the current login

I'm using express edition, compatibility set to 2005.

I'm logged in as DOMAIN\user, using windows authentication.

When I try to set the owner to this login, I get a "login does not exist on the server" error. Setting the owner to sa works fine.

Any fix to this problem ? Also - can I just work with the owner set to sa or can this cause problems in the future ?

Thanks.

If you could supply a little code that reproduces the error we could probably get an answer for you. What objects are you trying to own?

Yes, you can set the owner to sa, but then you have to work with the object as that login. It would be better to have that object owned by someone with only the rights that they absolutely need.

|||

The property that I'm trying to set is the database owner - in the database properties' files page. I need to do this in order to use diagrams, which are not available without a valid owner.

|||I see. Express doesn't allow diagrams.|||

Buck,

Thank you for trying to help.

The diagrams work fine - but the thing is that I have to set the owner to sa in order to work with them. The problem is that although I am logged in as DOMAIN\user, the server claims there is no such login when I try to set the database owner to that login.

|||

u may be login as Domain/User but in the database the user mapped to this login may be different .. check that...

SP_helpLogins 'Domian/Username' -- to get all the users mapped to this login

Madhu

|||

You know what - you're right! I was working on the older Express versions.

http://blogs.msdn.com/vsdata/archive/2004/09/30/236215.aspx

They have indeed put the diagrams back into Express.

Anyway, you seem to have a different issue, then. Have you tried putting the domain\user in the sysadmins group in SQL Server? I don't suggest leaving them there, but that might help you narrow the problem down. You only need to be in the db_owner role, but you can go high and bring the user account to that role later.

What message are you getting in the logs about this?

|||

I'm sorry, but I'm a newbie to sqlserver.

I guess I need to do some more reading before I can try to do these things (like putting the domain\user in the sysadmins group or using SP_helpLogins). I'm using Mannagement Studio Express and I can't find the correct way to do it.

sql

Cant See Tables in SQL Server 2000

This is a re-post as I didn't get a resolution to the problem
Using MDAC 2.8
I have set up my SQL Server and set SQL SERVER authentication and for some
reason when I connect through ODBC I can't see any of the tables. It logs
into SQL server just fine, I have set the appropriate rights to the tables
etc. If I log on as system admin I can see the tables but can't update any
of the data.
Any ideas?What utility/tool/program are you using? Are you sure you're being connecte
d
to the database that you're expecting?

> Using MDAC 2.8
> I have set up my SQL Server and set SQL SERVER authentication and for some
> reason when I connect through ODBC I can't see any of the tables. It logs
> into SQL server just fine, I have set the appropriate rights to the tables
> etc. If I log on as system admin I can see the tables but can't update an
y
> of the data.
> Any ideas?
>
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Hey Neil,
Thanks for the reply..
I am suing Access 97/2000/2003 to link to the tables. Again it connects
fine, but the tables are not displayed.
Thanks!
Barry
"Neil Pike" <neilpike@.compuserve.com> wrote in message
news:VA.000061cf.021cee1a@.compuserve.com...
> What utility/tool/program are you using? Are you sure you're being
connected
> to the database that you're expecting?
>
some
logs
tables
any
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq
>|||Barry - connect using query analyser (ISQLW) and see if you can see the
tables.

> I am suing Access 97/2000/2003 to link to the tables. Again it connects
> fine, but the tables are not displayed.
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

can't register server in enterprise manager

I have a SQL 2000 Server that I just recently moved into an AD domain from
an NT domain. We created new windows authentication accounts for all users
and things have worked fine except for a couple of people. Three people
can't access any databases.
On my PC, if I login as myself and go to EM I can register this server with
no problems. If I login as one of these users, I get the following when
trying to register the server:
A connection could not be established to <servername>
Reason: Login failed for user <domain>\<user>
Please verify SQL Server is running and check your SQL Server registration
properties and try again.
SQL is definitely running and it is not a password problem since I logged in
just fine to the PC. Also, the user has local Administrator access to the
PC. I don't understand why 3 out of 30 login accounts are doing this. Can
anyone help?
Thanks.Hello,
You may want to first create a new domain user and then add it as a SQL
login to test the sitaution. If the issue does not occur, it seems to be
some issue with the specific user accounts. You may want to try following
steps for a problemtic domain login.
1. Remove all SQL logins with similar name from SQL logins
2. Remove the domain user from the AD
3. Try to create a new domain user with the same name, and add it again as
a SQL login.
4. Test if the issue still occurs at this time.
If the issue perists, you may try the following steps to isolate the issue:
1. Add the following registry keys:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters
DisableStrictNameChecking DWORD Value=1
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
DisableLoopbackCheck DWORD
Value=1
2. Edit the Local Security policy and set ?Network Security: LAN Manager
authentication level? to ?Send LM and NTLM responses?
3. Test if the issue occurs at this time.
926642 Error message when you try to access a server locally by using its
FQDN or its CNAME alias after you install Windows Server 2003 Service Pack
1: "Access denied" or "No network provider accepted the given network path"
http://support.microsoft.com/default.aspx?scid=kb;EN-US;926642
If you have any update on the issue, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||r
Take a look into ERROR.LOG , what is says?
When you create a login for one of those people what default database you
granted for them? Does it exist? Perhaps it is in suspect mode?
"r" <none@.none.com> wrote in message
news:uqolHls%23HHA.3900@.TK2MSFTNGP02.phx.gbl...
>I have a SQL 2000 Server that I just recently moved into an AD domain from
>an NT domain. We created new windows authentication accounts for all users
>and things have worked fine except for a couple of people. Three people
>can't access any databases.
> On my PC, if I login as myself and go to EM I can register this server
> with no problems. If I login as one of these users, I get the following
> when trying to register the server:
> A connection could not be established to <servername>
> Reason: Login failed for user <domain>\<user>
> Please verify SQL Server is running and check your SQL Server
> registration properties and try again.
> SQL is definitely running and it is not a password problem since I logged
> in just fine to the PC. Also, the user has local Administrator access to
> the PC. I don't understand why 3 out of 30 login accounts are doing this.
> Can anyone help?
> Thanks.
>

Can't make a new Login

Hi,
I have this problem:
When I create a new Login with a "SQL server authentication".
And if I change some User Mapping and Server Roles it gives me the follow
message "Create failed for User 'win2kadmin "
Server Name: SERVER\SERVER
Error Number: 15023
Severity: 16
State: 1
Line Number: 1
I will be gratful if you can help me,
--
Thanks ,
PeterThat error means the user, group, or role already exists. You will need to
drop the current user before creating the new one.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:34092E0F-DBB2-4DD4-BFE7-27E323E7A606@.microsoft.com...
> Hi,
> I have this problem:
> When I create a new Login with a "SQL server authentication".
> And if I change some User Mapping and Server Roles it gives me the follow
> message "Create failed for User 'win2kadmin "
> Server Name: SERVER\SERVER
> Error Number: 15023
> Severity: 16
> State: 1
> Line Number: 1
> I will be gratful if you can help me,
> --
> Thanks ,
> Peter

Can't make a connection to SQL Express as Server does not exist or access denied...

Hi

I have recently downloaded SQL Server Express which I have installed using Windows authentication mode. I cannot seem to be able to make a connection to SQL Server from Dreamweaver or Microsoft Visual Web Developer as I am getting an error Server does not exist or access is denied....

I am relatively new to all of this, so would appreciate any advice....

When I installed SQL Server Express I chose Windows Authentication. When I fire up SQL Express and view Security option - the installation program has set up an sa login with a random password. I did not set this password, but think this might be a reason why I can't connect. To rectify the problem I tried creating a new user in SQL Server Express with a password that I specified. On going in to check the settings, I notice SQL Server Express has gone and changed it from the password that I set up.

Now when I try and connect to SQL Server Express I specify the following in the connection paramaters

Server: BGIRL\SQLEXPRESS

Database The database name that I've created

User: the new user that I created

Password: the password I created as part of new user setup

Now I get error message Server does not exist or access denied

Is the problem to do with passwords or perhaps one of the many parameters one seems to have to set up? How can I change a password if I didn't create it? Is there anywhere I can reset it?

PLEASE HELP - I have literally spent 3 days attempting to get this working and am about to give up entirely. Due to my lack of experience, I need very specific step by step instructions..

DId you enable remote connections ? For a step by step guide, visit my site on the link below and visit the Screencast section, you will find the Enable Remote Connection on the list. Feel free to put a comment in there if you find it useful.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de|||

Thank you for your quick response. I viewed some of your screencasts, and they are very useful...

I had already enabled remote connections so this was not the problem, started up the SQL Browser too, and no luck.

I believe my problem is to do with SQL Express automatically setting up an sa password for me, I can't seem to be able to change this - any ideas as to how I can get around it? Also, when I create my own user in SQL Express, the software automatically changes the password so that I can't use the new user either. Is there a setting I need to change?

Regards Bron

|||There might be a firewall issue with your computer. By default SQL Server is listening on 1433 and uses 1434 additionally. Make sure these ones are not blocked. SQL Server won′t chnage the passwords for you, if you mean that after creating a new user the numbers of stars / dots in the password field differs from the one you entered, that is just for obfuscating the real length of the password and just means that there i something secure store, you don′t need to care about that.

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de

cant login to SQL Server anymore

Hi guys-
I can't seem to be able to login into SQL Server anymore ever since I c hanged my password for my Win 2k login. the authentication mode (i checked in the properties section ) is set to SQL Server authentication, but i know originally i set it up as Win 2k authentication, and then changed that to SQL Server authentication...the local server (its set up on my machine) will not start up any more, and I am clueless as to how to fix it. I've tried to login using my other ids ( that i've created) but i don't think those id's had sa rights, so it won't let me.

Please help!Hi Indigo4267,
Try the checking the following:
1). Using the 'Client Network Utility' in the MS Menu, click on the
'Alias' tab.

2). If you don't see your DB server in this list, add it as a Named Pipes
alias.

You should be able to logon direct now, since the DB is on your
local workstation.

3). Check to see what domain/user privileges you have as a user in
the domain that is running the MSSqlServer process. You may not
be able to 'see' the domain running the server.

Good luck!|||thanks for the advice. i'm really new to this db admin stuff (and sql server in general) so i only understood part 1 of what you wrote, and i followed your steps, but it still doesn't work...<sigh>

i guess the real underlying problem is "is there a way to change your win 2k password (if you are the db admin) and still be able to login into sql server (if its set up on your machine) regardless of what authentication mode is set up?" even when i switched to win authentication from sql authentication/mixed mode authentication it still failed!

:( i always get weird problems like this in everything i touch :(|||I actually managed to solve this problem...i think my SQL Server was set to mixed mode which is why i was having problems with my new win2k password--

here's how i did it (for others who are facing teh same problem)
in win2k-
-go to the control panel
-click on administrative tools
-click on services
-right click on MSSQLServer and choose properties
-click on logon tab
-enter and confirm new password
-restart SQL Server

its actually in the help section (for SQL Server 2k) under the heading "Changing Passwords and User Accounts" and there also is info there for win 4.0 NT.

Can't login as SA

SQL Server and Windows authentication is/has been
checked. Nothing changed.

>--Original Message--
>
>It
>Are you sure you are running 'SQL Server and Windows'
>authentication? Also, if you made the change did you stop
>and restart the MSSQLServer service?
>Steve
>.
>Hi,
Can you please restart the SQL Server Service and try.
Thanks
Hari
MCDBA
"sully" <anonymous@.discussions.microsoft.com> wrote in message
news:ffca01c3f25d$bae9f140$a401280a@.phx.gbl...
> SQL Server and Windows authentication is/has been
> checked. Nothing changed.
>
>

Can't Install SP2

I'm trying to install SP2 (on XP SP2) and for some reason I'm getting an error on the authentication dialog.

The error is: "Your account information could not be verified. Press Ok to return to Authentication Mode screen to determine the reason for failure." For setup to verify your credentials the services must be startable, connectable and you must be a SQL Server administrator." In the dialog under status the value reads "Login Failed: ".

I've tried integrated security (I'm a local admin on this machine) and the sa account but no luck either way. I've tried starting every service that starts with "SQL" and still no luck.

Thoughts or ideas?

Thanks,
Mike

Have you been able to solve this. I'm running into the same problem.

thanks

|||Yes, I changed my reg key for the default location of Program Files and that was my issue.

The installer must not have been looking at the current location for SQL but assuming it was under ProgramFilesDir.

Mike|||

If you are using Backup Exec to back up your databases, you need to add permissions to any files with "$4dir" at the end that reside in your DATA folder.

You can then install SP2 normally. This problem cropped up with SP1 as well and it took me days to figure this one out.

|||How did you can the reg key for the defauly location of program files?

Can't Install SP2

I'm trying to install SP2 (on XP SP2) and for some reason I'm getting an error on the authentication dialog.

The error is: "Your account information could not be verified. Press Ok to return to Authentication Mode screen to determine the reason for failure." For setup to verify your credentials the services must be startable, connectable and you must be a SQL Server administrator." In the dialog under status the value reads "Login Failed: ".

I've tried integrated security (I'm a local admin on this machine) and the sa account but no luck either way. I've tried starting every service that starts with "SQL" and still no luck.

Thoughts or ideas?

Thanks,
Mike

Have you been able to solve this. I'm running into the same problem.

thanks

|||Yes, I changed my reg key for the default location of Program Files and that was my issue.

The installer must not have been looking at the current location for SQL but assuming it was under ProgramFilesDir.

Mike
|||

If you are using Backup Exec to back up your databases, you need to add permissions to any files with "$4dir" at the end that reside in your DATA folder.

You can then install SP2 normally. This problem cropped up with SP1 as well and it took me days to figure this one out.

|||How did you can the reg key for the defauly location of program files?

Cant get windows authentication in Mixed Mode to work .... any ideas?

Hey all,
I've got SQL 2K running in mixed mode. I created a windows acct (mytest) in the administrator group, and then created a corresponding SQL account (mytest) using the windows authentication. In the EM it comes up as a "Windows User" type of acct. (The account is not a domain account but a workstation one).

I've configured my app to use that account for access to the database but I get the following error message when I send the connection request to the datasource:

Connection Failed:
SQL State:28000
SQL Server Error: 18456
Login failed for user myworkstation\mytest

I can connect just fine using the sql accounts, but it doesn't want to play nice when I need the windows authentication portion to work.
What exactly am I doing wrong?
Any input is greatly appreciated !
Regards,
TammyA couple of things come to mind...

1. If mytest is a member of the workstation Administrators group, then you do not (necessarily) need a separate SQL account for it. The BUILTIN\Administrators account permits all members of the local Administrators group to have access (in the default config anyway).

2. If you have denied access to the BUILTIN\Administrators group, then myWorkstation\mytest will not have access (Deny trumps permit).

3. Just a head check: you said that myworkstation\mytest was NOT a domain account (when you set it up). You didn't by chance login under the domain?

Just my $ .02. It's not much, I'll grant you, but it was what I could come up with.

hmscott|||Mr. Scott !
I'm such an idiot ....
I had forgotten that I denied access to the builtin Administrator account. I turned it back on and VOILA !
Mr. Scott - 1
Tammy - 0
Gracias !

Originally posted by hmscott
A couple of things come to mind...

1. If mytest is a member of the workstation Administrators group, then you do not (necessarily) need a separate SQL account for it. The BUILTIN\Administrators account permits all members of the local Administrators group to have access (in the default config anyway).

2. If you have denied access to the BUILTIN\Administrators group, then myWorkstation\mytest will not have access (Deny trumps permit).

3. Just a head check: you said that myworkstation\mytest was NOT a domain account (when you set it up). You didn't by chance login under the domain?

Just my $ .02. It's not much, I'll grant you, but it was what I could come up with.

hmscott

Can't Get SQL Authentication to work With XP Pro SP2 in very simple test case

I'm trying to work my way through the steps of using a User Id and Password in a connection string.

I'm working with SQL 2005 Express, VS2005, in the development server. Got an error I can't get around...tried it several diffent ways on a slightly more complicated test site...no joy...so went to the MSDN tutorial...made the most "vanilla" test I could think of, and still can't figure it out.

I thought it would be simple enough that I could post the whole thing (below)

The test works fine with Integrated Security = True in the connection string. When I remove that phrase, I get the error:

{"CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file E:\MyPath\App_Data\VSST_DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."}

This occurs on the cn.Open statement below.

It gets past the login, so I know that the SQL User and password match up correctly.

==========================
<connectionStrings>
<add name="VSST_CN"
connectionString="Data Source=MyServer\SQLEXPRESS;AttachDbFilename=E:\MyPath\App_Data\VSST_DB.mdf;User Id = VSST; Password=vsst123"
providerName="System.Data.SqlClient"/>
</connectionStrings>

=========================
Page Code Behind (no controls on page)

Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("VSST_CN").ToString())

Dim cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM VSST_Table", cn)
cn.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
rdr.Read()
Response.Write(rdr(0).ToString())

End Sub
End Class

=================
The DB

Table VSST_Table,
ID is int, primary key, identity
Field1, Field2, Field3, Field4, Field5 are varchar(50)

I added one record ("A", "B", "C", "D", "E") to the table through VS2005 Server Explorer

This shows up in Count = 1 from running the page when Integrated Security = True

=================
In SSMSE: (this is ALL I did, tried to use the minimum so not to confuse...)

I added the SQL Authentication Server level user "VSST" with the password "vsst123" (and the login works, as noted above)

I attach the .mdf

I add VSST to the Database Users, and give it db_owner

I add VSST to the Table with all permissions checked.

=====================================

I can't figure this out. This is a very vanilla test and I'm stumped. I'm about to give up on SQL Authentication entirely (at least for now), and just try to filter my inputs for SQL Injections...that's the only reason I have (at this stage in my biz plan) for needing SQL Authentication. On the other hand, I really don't like being this stumped on something that is so widely promoted as a common practice.

Any help on this would be greatly appreciated.

Thanks!

It's an XP SP2 security issue. The connect is running within an application sandbox which is not granted the authority to attach, create, or restore a database. The integrated security scenario works, because the Windows credentials are passed through. We spent days trying to figure a way around this one when dealing with a click once application installation and finally gave up. There wasn't a single permutation that we could use to get around the security restrictions that XP SP2 put in place. If the database already existed, everything connected and worked just fine. But, it simply refused to attach a database.|||

Thanks for the input. Wow. Good to know I wasn't completely blowing it....it looked too easy.

So, maybe it will work on the Windows 2003 VPS Server I'm renting...I guess I'll have to give it a try. But maybe it tells me I need to consider why SQL Authentication is critical to this app.

Grazie!

|||

I think there was something in the decision tree that I missed...

As I am gradually putting this together, it looks like the first choice is "1 server => windows authentication" vs "multiple servers (plus some other cases) => sql authentication"

After that, everything that is written about sql authentication seems to assume a level of professional expertise which is appropriately associated with a multi-server environment. Which probably means that the developers may have XP on their desks, but that they're logging into an W2003 development environment...so they never get exposed to this problem (or are aware of it and bypass it).

If you miss the first branch in the decision tree, this is never mentioned...it's all about encrypting web.config and guarding against sql injection by limiting table access, clever ways to use trusted this and that....but never "maybe you should just use Windows Authentication, if it's a single server app."

Long way to the first u-turn....hazards of being a newbit!

|||

Similar grief here

http://www.mcse.ms/message2347651.html

Can't Get SQL Authentication to work in very simple test case

I'm trying to work my way through the steps of using a User Id and Password in a connection string.

I'm working with SQL 2005 Express, VS2005, in the development server. Got an error I can't get around...tried it several diffent ways on a slightly more complicated test site...no joy...so went to the MSDN tutorial...made the most "vanilla" test I could think of, and still can't figure it out.

I thought it would be simple enough that I could post the whole thing (below)

The test works fine with Integrated Security = True in the connection string. When I remove that phrase, I get the error:

{"CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file E:\MyPath\App_Data\VSST_DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."}

This occurs on the cn.Open statement below.

It gets past the login, so I know that the SQL User and password match up correctly.

==========================
<connectionStrings>
<add name="VSST_CN"
connectionString="Data Source=MyServer\SQLEXPRESS;AttachDbFilename=E:\MyPath\App_Data\VSST_DB.mdf;User Id = VSST; Password=vsst123"
providerName="System.Data.SqlClient"/>
</connectionStrings>

=========================
Page Code Behind (no controls on page)

Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("VSST_CN").ToString())

Dim cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM VSST_Table", cn)
cn.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
rdr.Read()
Response.Write(rdr(0).ToString())

End Sub
End Class

=================
The DB

Table VSST_Table,
ID is int, primary key, identity
Field1, Field2, Field3, Field4, Field5 are varchar(50)

I added one record ("A", "B", "C", "D", "E") to the table through VS2005 Server Explorer

This shows up in Count = 1 from running the page when Integrated Security = True

=================
In SSMSE: (this is ALL I did, tried to use the minimum so not to confuse...)

I added the SQL Authentication Server level user "VSST" with the password "vsst123" (and the login works, as noted above)

I attach the .mdf

I add VSST to the Database Users, and give it db_owner

I add VSST to the Table with all permissions checked.

=====================================

I can't figure this out. This is a very vanilla test and I'm stumped. I'm about to give up on SQL Authentication entirely (at least for now), and just try to filter my inputs for SQL Injections...that's the only reason I have (at this stage in my biz plan) for needing SQL Authentication. On the other hand, I really don't like being this stumped on something that is so widely promoted as a common practice.

Any help on this would be greatly appreciated.

Thanks!

It's an XP SP2 security issue. The connect is running within an application sandbox which is not granted the authority to attach, create, or restore a database. The integrated security scenario works, because the Windows credentials are passed through. We spent days trying to figure a way around this one when dealing with a click once application installation and finally gave up. There wasn't a single permutation that we could use to get around the security restrictions that XP SP2 put in place. If the database already existed, everything connected and worked just fine. But, it simply refused to attach a database.|||

Thanks for the input. Wow. Good to know I wasn't completely blowing it....it looked too easy.

So, maybe it will work on the Windows 2003 VPS Server I'm renting...I guess I'll have to give it a try. But maybe it tells me I need to consider why SQL Authentication is critical to this app.

Grazie!

|||

I think there was something in the decision tree that I missed...

As I am gradually putting this together, it looks like the first choice is "1 server => windows authentication" vs "multiple servers (plus some other cases) => sql authentication"

After that, everything that is written about sql authentication seems to assume a level of professional expertise which is appropriately associated with a multi-server environment. Which probably means that the developers may have XP on their desks, but that they're logging into an W2003 development environment...so they never get exposed to this problem (or are aware of it and bypass it).

If you miss the first branch in the decision tree, this is never mentioned...it's all about encrypting web.config and guarding against sql injection by limiting table access, clever ways to use trusted this and that....but never "maybe you should just use Windows Authentication, if it's a single server app."

Long way to the first u-turn....hazards of being a newbit!

|||

Similar grief here

http://www.mcse.ms/message2347651.html