[AccessD] OT: MS SQL question

Jim,

This (cribbed from my site artfulsoftware.com) may be of use:

Change authentication mode Change the Authentication mode on existing MSDE
Servers to 'mixed' (Windows Authentication and SQL Server) by modifying this
registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.x\MSSQLServer

If you are using SQL Server 2005, the above registry key stores the
authentication mode regardless of whether you installed a default instance
or a named instance. MSSQL.x is a placeholder for the corresponding value
for your system.

Set the value LoginMode to 2. This tells your server to accept SQL Server
AND Windows authentication methods.

Stop and restart the MSSQL service after making this change:

NET STOP MSSQLSERVER
NET START MSSQLSERVER

To accomplish the same for every server instance using the Windows GUI, log
in via Windows Authentication. If you can't log on via Windows Auth then
revise User Account privileges in Control Panel | User Accounts. Check to
see if you have Administrator Access on machine. If not, set it and continue
below.

1. Right click 'your server', choose 'properties'. Go to 'Security' and
select 'Mixed Mode'. Close SQL EMT.
2. In Start | Control Panel | Admin Tools | Services panel, scroll down to
view your SQL services and then one-at-a-time right click each and select
properties.
3. Click the 'Log On' tab and select 'Local System account' and 'Allow
service to interact..' check box (nothing else), then click 'OK'.
4. You should be back at the main Sevices panel. Using the cassette player
icons at the top of panel, simply stop, then start each SQL service (there's
a stop start button does both auto).
5. After restarting each, close window and try logging in using SQL Auth.
For the real beginners: username: sa pwd: leave blank.

hth,
Arthur

On Sat, Feb 9, 2008 at 9:49 PM, Jim Lawrence <accessd at shaw.ca> wrote:

> Sorry for the OT but I am having a bad MS SQL day and responses have been
> slow on the other list.
>
> I have always designed my MS SQL with Windows Authentication and when
> Anonymous access was required it was through IIS... for the last 10 years.
>
> Now there is a new challenge. Connect MS SQL 2005 through MS SQL or 'sa'
> mode and the server is proving a worthy opponent.
>
> The server is set to support both connection modes but I am not sure
> whether
> these setting can be set/unset at the database level. I have created a
> user
> and the system seems happy but when testing the connection through ODBC it
> responds with something like:
>
> Connection failed:
> SQLState: '42000'
> SQL Server Error: 4064
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default
> database. Login failed.
>
> I am sure the issues is something very simple but after 2 days no joy.
>
> Any insights?
>
> TIA
> Jim
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>