MSDE access denied error

I set up MSDE that came with VS.NET and, using the Server Explorer, created a database called 'pubs' on my server. The MSSQLServer icon is lit up in the System Tray and has been started. When I created 'pubs', I was prompted whether I wanted to use NT Integrated Security or SQL Server Authentication. I was forced to choose the NT option because any login name that I used, including my administrator account, the 'aspnet_wp account', 'SQLDebugger' and 'sa'. Every time I tried to use the SQL Server Auth. option, I would get the following error prompt:

ADO Error: '
Login failed for user 'whateverItried'. Reason: Not associated with a trusted SQL Server connection.

When running the following:

Dim myConnection As SqlConnection = New SqlConnection("server=(local);database=pubs;uid=sa;pwd=")

it would result in this error message:

Index #0
Error: System.Data.SqlClient.SqlError: SQL Server does not exist or access denied.

I also tried using 'localhost' and <my-computername>\<my-servername> for the server. For my database name, I tried both pubs and dbo.pubs. I also tried every username I could think of.

No luck.

Here's the funny thing: I was able to create a View on the database that did exactly what I was trying to do in code, so the DB is working.

If anyone has sucessfully ran the first example program in Chapter 16 of Beginning VB.NET 2nd edition, please respond. That's where I got this example.

Thanks for any help!

JasonWhat is the authentication mode used to connect?

Refer to books online for Troubleshooting the Transact-SQL Debugger topic.|||Originally posted by Satya
What is the authentication mode used to connect?

Refer to books online for Troubleshooting the Transact-SQL Debugger topic.

I'm not sure what you are asking. Remember that I'm using MSDE and I don't go through a lot of the setup that MS SQL uses.

I was forced to use the NT Integrated Security, since no usernames or password combinations that I knew would work for the SQL option, if that's what you mean.|||True, update the SQL Server Authentication mode back to SQL Server Authentification as referred in this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;285097).|||I have only one icon running in my system tray and it is set to SQL Server, not SQL Server Agent.

Do I need both running? If so, how can I get an instance of Agent to run as well as the server, as suggested in the KBA?

By the way, thanks for your response!

Jason|||Also, what username do I use? 'SA'?

Jason

Originally posted by jtimms
I have only one icon running in my system tray and it is set to SQL Server, not SQL Server Agent.

Do I need both running? If so, how can I get an instance of Agent to run as well as the server, as suggested in the KBA?

By the way, thanks for your response!

Jason|||We have the same issue. If you also have Enterprise Manager installed on the same machine, use it to be sure TCP/IP is an enabled protocol. We did that and were able to connect.

The 64 dollar question is how you do this if you DON'T have Enterprise manager. Anyone know how to do it programatically?

Jim M.
Orrtax Software|||Of course you can!

Search for this file:

svrnetcn.exe

usually in:

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe"

which is the Server Network Configuration|||Manowar...thank you! I had the same problem as above, and it turned out that my server had not had TCP/IP enabled! Thank you so much!|||hi manomar - thanks a lot for your entry to this forum. i have had exactly the same problem and thru your entries - now it works!

regards,

Originally posted by manowar
Of course you can!

Search for this file:

svrnetcn.exe

usually in:

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe"

which is the Server Network Configuration|||MSDE usually installs as a named instance, rather than the default instance (this way no two MSDE installs can interfere with each other...theoretically). Check in Administrative tools->services, and check the name of the MSSQL service. If it is MSSQLService, then it is amazingly the default instance. If it is MSSQL$blah, then you have a named instance. In order to address a named instance, you need to specify the name of the server, and the name of the instance as

server=servername\blah

You can also use IP addresses:

10.10.10.10\blah

Not sure if this works:

(local)\blah

Note that while the service is named with a '$', and all folders related to the instance are named with a '$', the service is addressed with '\'. Intuitive, right?|||I have another strange problem:

When I use SVRNETCN or click on the properties in enterprise manager, I see that TCP/IP is enabled for my msde Instance.
But when I look in the error.log of my instance, I see only the entry:
SQL server listening on Named Pipes

There is no entry that the server is listening on TCP/IP and I still get the error message "SQL Server does not exist or access denied" when I try to connect to my database over the network.
So I think my server is still not listening on TCP/IP.

Has anyone a workaround for this problem?

Thanks in advance

MSDB and PUBS Databases

We are running SQL 2000 Enterprise Edition on Windows 2003 Standard Server.
We setup a SQL backup to run under SQL. We got the messages in the report.
It said "backup cannot be performed on database 'msdb'. This subtask is
ignored." and "Backup cannot be performed on database 'pubs'. This subtask
is ignored". Is it necessary to backup msdb and pubs databases? Thanks.Hi Diane
My guess is that this error is occurring when you try to back up the
transaction log of these databases, as they are in simple mode and the log
cannot be backed up.
Pubs is a sample database used in the documentation examples, and can be
easily rebuilt. It should never need to be backed up at all. MSDB should be
backed up so you can restore information about your tasks, jobs, alerts and
backup history. But you may be backing up the full database already and it
may only be the log backup that is failing.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:utYsaWJOGHA.2472@.TK2MSFTNGP11.phx.gbl...
> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
> Server. We setup a SQL backup to run under SQL. We got the messages in
> the report. It said "backup cannot be performed on database 'msdb'. This
> subtask is ignored." and "Backup cannot be performed on database 'pubs'.
> This subtask is ignored". Is it necessary to backup msdb and pubs
> databases? Thanks.
>|||MSDB stores all you job as well as replication information, so backin
this up can save you lot of time in a disastery case. Pubs is jut a
sample database, if you want to you can leave this out, as you can
download the creation script somewhere on the MSDN space of microsoft.
HTH, Jens Suessmeyer.|||Thank you very much, Kalen for your prompt response.
Under Database Maintenance, a complete backup runs once a week and
transaction backup runs everyday. You are right. The error message comes
from the transaction log history. Do you suggest that I ignore the error
messages since I have a complete backup runs every week? I will uncheck the
backup for the PUBS database.
Thanks.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eyN$1mJOGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi Diane
> My guess is that this error is occurring when you try to back up the
> transaction log of these databases, as they are in simple mode and the log
> cannot be backed up.
> Pubs is a sample database used in the documentation examples, and can be
> easily rebuilt. It should never need to be backed up at all. MSDB should
> be backed up so you can restore information about your tasks, jobs, alerts
> and backup history. But you may be backing up the full database already
> and it may only be the log backup that is failing.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:utYsaWJOGHA.2472@.TK2MSFTNGP11.phx.gbl...
>
>|||Thanks for your prompt response, Jens. I just unchecked the backup for the
PUBS database. Thanks.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1140713647.322715.266680@.g47g2000cwa.googlegroups.com...
> MSDB stores all you job as well as replication information, so backin
> this up can save you lot of time in a disastery case. Pubs is jut a
> sample database, if you want to you can leave this out, as you can
> download the creation script somewhere on the MSDN space of microsoft.
> HTH, Jens Suessmeyer.
>

MSDB and PUBS Databases

We are running SQL 2000 Enterprise Edition on Windows 2003 Standard Server.
We setup a SQL backup to run under SQL. We got the messages in the report.
It said "backup cannot be performed on database 'msdb'. This subtask is
ignored." and "Backup cannot be performed on database 'pubs'. This subtask
is ignored". Is it necessary to backup msdb and pubs databases? Thanks.
Hi Diane
My guess is that this error is occurring when you try to back up the
transaction log of these databases, as they are in simple mode and the log
cannot be backed up.
Pubs is a sample database used in the documentation examples, and can be
easily rebuilt. It should never need to be backed up at all. MSDB should be
backed up so you can restore information about your tasks, jobs, alerts and
backup history. But you may be backing up the full database already and it
may only be the log backup that is failing.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:utYsaWJOGHA.2472@.TK2MSFTNGP11.phx.gbl...
> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
> Server. We setup a SQL backup to run under SQL. We got the messages in
> the report. It said "backup cannot be performed on database 'msdb'. This
> subtask is ignored." and "Backup cannot be performed on database 'pubs'.
> This subtask is ignored". Is it necessary to backup msdb and pubs
> databases? Thanks.
>
|||MSDB stores all you job as well as replication information, so backin
this up can save you lot of time in a disastery case. Pubs is jut a
sample database, if you want to you can leave this out, as you can
download the creation script somewhere on the MSDN space of microsoft.
HTH, Jens Suessmeyer.
|||Thank you very much, Kalen for your prompt response.
Under Database Maintenance, a complete backup runs once a week and
transaction backup runs everyday. You are right. The error message comes
from the transaction log history. Do you suggest that I ignore the error
messages since I have a complete backup runs every week? I will uncheck the
backup for the PUBS database.
Thanks.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eyN$1mJOGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi Diane
> My guess is that this error is occurring when you try to back up the
> transaction log of these databases, as they are in simple mode and the log
> cannot be backed up.
> Pubs is a sample database used in the documentation examples, and can be
> easily rebuilt. It should never need to be backed up at all. MSDB should
> be backed up so you can restore information about your tasks, jobs, alerts
> and backup history. But you may be backing up the full database already
> and it may only be the log backup that is failing.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:utYsaWJOGHA.2472@.TK2MSFTNGP11.phx.gbl...
>
>
|||Thanks for your prompt response, Jens. I just unchecked the backup for the
PUBS database. Thanks.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1140713647.322715.266680@.g47g2000cwa.googlegr oups.com...
> MSDB stores all you job as well as replication information, so backin
> this up can save you lot of time in a disastery case. Pubs is jut a
> sample database, if you want to you can leave this out, as you can
> download the creation script somewhere on the MSDN space of microsoft.
> HTH, Jens Suessmeyer.
>

MSDB and PUBS Databases

We are running SQL 2000 Enterprise Edition on Windows 2003 Standard Server.
We setup a SQL backup to run under SQL. We got the messages in the report.
It said "backup cannot be performed on database 'msdb'. This subtask is
ignored." and "Backup cannot be performed on database 'pubs'. This subtask
is ignored". Is it necessary to backup msdb and pubs databases? Thanks.MSDB stores all you job as well as replication information, so backin
this up can save you lot of time in a disastery case. Pubs is jut a
sample database, if you want to you can leave this out, as you can
download the creation script somewhere on the MSDN space of microsoft.
HTH, Jens Suessmeyer.|||Hi Diane
My guess is that this error is occurring when you try to back up the
transaction log of these databases, as they are in simple mode and the log
cannot be backed up.
Pubs is a sample database used in the documentation examples, and can be
easily rebuilt. It should never need to be backed up at all. MSDB should be
backed up so you can restore information about your tasks, jobs, alerts and
backup history. But you may be backing up the full database already and it
may only be the log backup that is failing.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:utYsaWJOGHA.2472@.TK2MSFTNGP11.phx.gbl...
> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
> Server. We setup a SQL backup to run under SQL. We got the messages in
> the report. It said "backup cannot be performed on database 'msdb'. This
> subtask is ignored." and "Backup cannot be performed on database 'pubs'.
> This subtask is ignored". Is it necessary to backup msdb and pubs
> databases? Thanks.
>|||Thank you very much, Kalen for your prompt response.
Under Database Maintenance, a complete backup runs once a week and
transaction backup runs everyday. You are right. The error message comes
from the transaction log history. Do you suggest that I ignore the error
messages since I have a complete backup runs every week? I will uncheck the
backup for the PUBS database.
Thanks.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eyN$1mJOGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi Diane
> My guess is that this error is occurring when you try to back up the
> transaction log of these databases, as they are in simple mode and the log
> cannot be backed up.
> Pubs is a sample database used in the documentation examples, and can be
> easily rebuilt. It should never need to be backed up at all. MSDB should
> be backed up so you can restore information about your tasks, jobs, alerts
> and backup history. But you may be backing up the full database already
> and it may only be the log backup that is failing.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:utYsaWJOGHA.2472@.TK2MSFTNGP11.phx.gbl...
>> We are running SQL 2000 Enterprise Edition on Windows 2003 Standard
>> Server. We setup a SQL backup to run under SQL. We got the messages in
>> the report. It said "backup cannot be performed on database 'msdb'. This
>> subtask is ignored." and "Backup cannot be performed on database 'pubs'.
>> This subtask is ignored". Is it necessary to backup msdb and pubs
>> databases? Thanks.
>>
>
>|||Thanks for your prompt response, Jens. I just unchecked the backup for the
PUBS database. Thanks.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1140713647.322715.266680@.g47g2000cwa.googlegroups.com...
> MSDB stores all you job as well as replication information, so backin
> this up can save you lot of time in a disastery case. Pubs is jut a
> sample database, if you want to you can leave this out, as you can
> download the creation script somewhere on the MSDN space of microsoft.
> HTH, Jens Suessmeyer.
>