My Server alias has stopped working. in VB.Net

I've set up an alias for my laptop-clone of the production server with
the same name as that server (since I work disconnected most often).
This has worked swimmingly in the past. I went into Client Network
Utilities, "Added" a new alias, set the protocol to TCPIP, specified
an alias with the same name as my production server, and assigned it
to point to my local SQL Server instance.
Suddenly, this has stopped working, for some reason. Oddly, I can
register the server in Enterprise manager, but when I use the
connectionstring:
"Integrated Security=SSPI;Persist Security Info=False;Initial
Catalog=MyDatabase;Data Source=" & SERVERNAME
-- where SERVERNAME is a constant for the alias/production server
name, I get a "SQL Server does not exist or access denied".
I've been racking my brains (both of them) trying to figure out what's
changed in my environment, but am coming up empty. It just doesn't
want to connect now.
Any ideas?
As a solo programmer, the worst thing in the world is sitting down for
a day's work only to spend hours solving basic connectivity issues
totally unrelated to your work product. Gaah.
BLink
Brian Link <blink@.visi.com> wrote in
news:9f5q215937ft3402d28issffja1ufc0ok3@.4ax.com:

> I've set up an alias for my laptop-clone of the production server with
...
> I've been racking my brains (both of them) trying to figure out what's
> changed in my environment, but am coming up empty. It just doesn't
> want to connect now.
> Any ideas?
> As a solo programmer, the worst thing in the world is sitting down for
> a day's work only to spend hours solving basic connectivity issues
> totally unrelated to your work product. Gaah.
...
Brian,
The first thing to do is to validate if this is an issue with the App, or
an issue with the configuration of SQL Server. As I mentioned in a
different group, you want to first make sure that the SQL Server service
isn't running under the local system service account.
First things first, go to the server itself and check and make sure
security is setup. In SQL Server there are "Logins" and there are
"Users". Users follow the database, logins follow the server. And Logins
are associated with Users. If you just loaded a backup of the database,
or just attached it on the new SQL Server, you may have a problem with
disassociated logins and users.
Go to a box with enterprise manager that can talk to the server and open
the server/security section. Locate the login in the list that you are
using to connect to the server via your app. (Is it a Windows User, or
Windows Group? or does it say "Standard".. I.e. What's the "type")
Open up that user and click on the Database Access tab and see what
databases that user is permitted to use and what their role in that
database is. If the database you built is not one that they are permitted
to use (but they should be) try and check the permit option and give them
at least public role. Click on O.K. If you get a message about that user
already existing in that database, you have a problem with disassociated
users and logins.
You can correct these by using a system stored procedure called
sp_change_users_login. You should read about it in the SQL Server Books
Online. The 'Auto_Fix' option may be all you need.
If the Permit box is already checked and looks fine. Then it's not likely
you have such a problem and you can test trying to connect to it. Log
into windows with the user that you want to use windows authentication to
connect to the box.
Then open SQL Server Query Analyzer and when prompted to connect to SQL
Server, choose the option to Connect Using "Windows authentication".
Click o.k. and see if you can connect. If you can't, then we have to dig
into it further. If you can, then try to select your database that you
use from the drop down list at the top of the window. (or type USE
"database name" and press the green arrow) Then try to select records
from one of your tables. If that all works, then the issue isn't with SQL
Server and is somewhere in your app.
Some other things to keep in mind. When you setup a Login, you specify a
"Default Database". By default that often is the MASTER database. Thus if
your app connects with that user account, if it doesn't explictly use the
proper database, it may try to do operations in the MASTER database that
it isn't allowed to or it may try to access objects that don't exist
there. If your app isn't explicitly picking the database, then make sure
the login you create is. (You may be stating this in your Catalog
component of the connection string in the application)
Hope this is of some help.
Mike
|||To troubleshoot, i would recomend deleting the entries in the following
registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\SuerSocketNetLib\LastConnect
This is where sql server caches the translation on the aliases.
See if you can still register the server in SQL EM after removing all the
cached entries.
Can you connect to the alias using query analyzer?
hth
Vikram Vamshi
Eclipsys Corporation
"Brian Link" <blink@.visi.com> wrote in message
news:9f5q215937ft3402d28issffja1ufc0ok3@.4ax.com...
> I've set up an alias for my laptop-clone of the production server with
> the same name as that server (since I work disconnected most often).
> This has worked swimmingly in the past. I went into Client Network
> Utilities, "Added" a new alias, set the protocol to TCPIP, specified
> an alias with the same name as my production server, and assigned it
> to point to my local SQL Server instance.
> Suddenly, this has stopped working, for some reason. Oddly, I can
> register the server in Enterprise manager, but when I use the
> connectionstring:
> "Integrated Security=SSPI;Persist Security Info=False;Initial
> Catalog=MyDatabase;Data Source=" & SERVERNAME
> -- where SERVERNAME is a constant for the alias/production server
> name, I get a "SQL Server does not exist or access denied".
> I've been racking my brains (both of them) trying to figure out what's
> changed in my environment, but am coming up empty. It just doesn't
> want to connect now.
> Any ideas?
> As a solo programmer, the worst thing in the world is sitting down for
> a day's work only to spend hours solving basic connectivity issues
> totally unrelated to your work product. Gaah.
> BLink