[dba-SQLServer] Problems registering database

MartyConnelly martyconnelly at shaw.ca
Wed Oct 13 02:14:16 CDT 2004


Came across this introductory article complete with sample Access 97 and 
2000 mdb's
that some may find useful for SQL Server or MSDE. Reguires "pub" test 
SQL database be installed.

How to Execute SQL Stored Procedures from Microsoft Access

The simplicity and popularity of SQL Server means that more and more
developers who build applications with Microsoft Access will want to learn
how to take advantage of server side processing using SQL Server Stored
Procedures. In this article, Danny Lesandrini demonstrates a simple method
for executing procedures from Access 97 and Access 2000.
This method uses SQL server password authentication rather than windows.

http://nl.internet.com/ct.html?rtr=on&s=1,14m8,1,aef3,djrw,dbyg,2122



Haslett, Andrew wrote:

>Quick overview of both the Authentication methods:
>
>* SQL Authentication:  Standard username and password access.  This means
>the username (login) must be created within SQL Server and given appropriate
>rights.  You must specify a username and password when you try to connect to
>SQL.
>
>* Windows Authentication:  Simplistically, your current Windows account
>credentials (username and password of the account you logged into Windows
>with) are basically passed to SQL Server in the background.  This account
>also needs to be given the appropriate access within SQL Server itself.  You
>therefore don't specify any username and password yourself when connecting.
>
>By Default the SQL Account (SA) and the Windows Accounts (Local
>Administrators Group) are given access, as well as a few others.
>
>
>  
>
>>>I also tried to use the property dialog of the local laptop on my laptop
>>>to set the startup service to sa and oh man what a mistake THAT was.
>>>      
>>>
>
>Unless running in a domain environment, the account that the MSSQLSERVICE
>service runs under (from services.msc) shouldn't really need to be anything
>else except the default ('Local System').  This account needs to have
>certain rights on the machine such as registry / file permissions that are
>set up when SQL is installed, so changing this to sa will stuff it up, since
>there is no such Windows User as 'SA'.
>
>If you are able to stop the service, change the account it runs under back
>to local system (through services.msc), and restart the service, you
>*should* be OK.  
>
>When a SQL Instance is set to SQL Authentication, you should still be able
>to register/login to EM using Windows Authentication.  This means if you are
>logged onto your machine using any account with local admin rights, you
>should be able to connect to the instance through EM using Windows
>Authentication.  (An Instance set to SQL Authentication allows both Windows
>and SQL Authentication.)
>
>If that doesn't work you should be able to specify the SA username and
>password when connecting through EM.  
>
>As for your other issues, if you're certain you changed all your instances
>to SQL Authentication (restarted the service) and know the password, then I
>can only this there's some type of DNS issue.  Perhaps try the IP address
>instead?
>
>A
>
>-----Original Message-----
>From: John W. Colby [mailto:jwcolby at colbyconsulting.com] 
>Sent: Tuesday, 12 October 2004 1:32 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [dba-SQLServer] Problems registering database
>
>Well, I spoke too soon.  Now Neo2 is pegging the CPU usage all the time, and
>EM can't even get in to it's own database (local).  Or more correctly it can
>see the db but when I try and click on databases it just hangs with the
>hourglass.  Which is a catastrophe!  Before I could at least use Neo2 from
>Neo2, now I can't do anything at all.
>
>Come to think of it, it may be trying in vain to roll back a transaction or
>something.  I had QA running an update query when I changed the login type
>property which forcefully shut down SQL Server.
>
>I think I'll go to bed and pray that this thing survives.
>
>John W. Colby
>www.ColbyConsulting.com 
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John W.
>Colby
>Sent: Monday, October 11, 2004 11:43 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [dba-SQLServer] Problems registering database
>
>
>Andrew,
>
>Well, halafrigginlula.  On Soltek1 I tried to register Neo1 and Neo2, using
>sa and the password.  Neo2 registered correctly, Neo1 gives me a "login
>failed for user sa".  Likewise, on Neo1 I registered Neo2 using sa and the
>password.  Soltek1 was already registered.  My laptop ColbyM6805 registered
>Neo2 using sa and the password but the registration of Neo1 and Soltek
>failed. From my wife's machine MaryDesktop I can see all the other servers
>but only Neo2 registered successfully.  Neo1 and Soltek1 failed with the
>"login failed for user sa" and M6805 failed with the infamous "guest".
>
>In fact this gets me 1/2 way there since the nVLDB physically resides on
>Neo2, I can now bang at it from Neo1, Soltek1, MaryDesktop and my Laptop.  I
>am definitely thrilled at the improvement in my situation however I'm also
>very uneasy that I have no clue why this "X registers but Y fails" is going
>on.
>
>I also tried to use the property dialog of the local laptop on my laptop to
>set the startup service to sa and oh man what a mistake THAT was.  Now I
>can't get the local database to login at all.  It tells me the login is
>broken or something (so true! 8(
>
>I can't get at the property dialog, I can't start the service manager,
>basically I am really hosed on that machine.  Which is a problem since a
>database for a project I am working on is on that machine.  Sigh.
>
>So any ideas how do I get back in to this database?
>
>Any ideas why the sa account works on Neo2 but not on Neo1 or Soltek1?
>
>Is there any way to just look at all the accounts like you can in Windows?
>If I could do that I might be able to compare machine to machine and see
>what the heck is going on here.  AFAIK I just told it the default install of
>SQL Server so I just don't understand why the responses are so different
>from machine to machine.
>
>John W. Colby
>www.ColbyConsulting.com 
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Haslett,
>Andrew
>Sent: Monday, October 11, 2004 10:45 PM
>To: 'dba-sqlserver at databaseadvisors.com'
>Subject: RE: [dba-SQLServer] Problems registering database
>
>
>You did it all correctly except the account isn't 'administrator', its
>actually just 'sa' (which stands for system administrator).  So the username
>is actually sa and the password is what you specified.
>
>The security risks you speak of are correct, as the sa account has full
>rights over the entire instance of SQL Server you are using, which you often
>don't require - however if you were to use Windows Auth, you'd still need to
>set up the login and access permissions for *an* account in SQL itself for
>it to work.
>
>Therefore whichever way you go, you'd still have to learn about logins /
>roles / security etc. and from what I understand of your requirements, you
>don't have time to learn or master this.
>
>Therefore, the sa account is the easiest for you to setup and use, as it
>will require no configuration of account / security etc -> and as (I think)
>you're on a (relatively) isolated network with hardware, software firewall /
>NAT etc., the security risks are no more severe than if someone hacked into
>your machine anyway.
>
>Cheers,
>A
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>IMPORTANT - PLEASE READ ******************** 
>This email and any files transmitted with it are confidential and may 
>contain information protected by law from disclosure. 
>If you have received this message in error, please notify the sender 
>immediately and delete this email from your system. 
>No warranty is given that this email or files, if attached to this 
>email, are free from computer viruses or other defects. They 
>are provided on the basis the user assumes all responsibility for 
>loss, damage or consequence resulting directly or indirectly from 
>their use, whether caused by the negligence of the sender or not.
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada






More information about the dba-SQLServer mailing list