how to associate sql login with a trusted sql server connection?

Hello,
Last night I loaded a 2nd instance (development instance) of sql server on
my server machine (win2003). The first instance is the local server - serv1
.
The 2nd instance is serv1\dev. I loaded both instances using Windows
authentication. Note: the first instance was loaded on the default port of
1433. But for the 2nd instance, Setup assigned a default port of 0. I went
with port 0 and windows authentication. When I connect to serv1\dev in Quer
y
Analyzer through Windows authentication I connect OK. But I added a sql
login to serv1\dev. I can't connect to serv1\dev from Query Analyzer using
my sql login. The error message says that the login failed because my login
is not associated with a trusted sql server connection. So how do I
associate my sql login with a trusted sql server connection?
Note: On the original install I also added a sql login, and it works fine
from the server computer and from remote workstations. Why did the serv1
login work but not my new login on serv1\dev?
Thanks,
RichAfter doing a little research, I believe my problem is that I loaded
serv1\dev instance of sql server with Windows Authentication where I should
have selected Sql Authentication. When I register serv1\dev I can only
register it using Windows Authentication. Once the server is registered I g
o
into Edit Registration and select Sql authentication but it won't take
because my sql login is not associated with a trusted connection. Is there
a
way around this? Or do I need to re-install this instance?
"Rich" wrote:

> Hello,
> Last night I loaded a 2nd instance (development instance) of sql server on
> my server machine (win2003). The first instance is the local server - ser
v1.
> The 2nd instance is serv1\dev. I loaded both instances using Windows
> authentication. Note: the first instance was loaded on the default port
of
> 1433. But for the 2nd instance, Setup assigned a default port of 0. I we
nt
> with port 0 and windows authentication. When I connect to serv1\dev in Qu
ery
> Analyzer through Windows authentication I connect OK. But I added a sql
> login to serv1\dev. I can't connect to serv1\dev from Query Analyzer usin
g
> my sql login. The error message says that the login failed because my log
in
> is not associated with a trusted sql server connection. So how do I
> associate my sql login with a trusted sql server connection?
> Note: On the original install I also added a sql login, and it works fine
> from the server computer and from remote workstations. Why did the serv1
> login work but not my new login on serv1\dev?
> Thanks,
> Rich|||OK. I figured out the problem. I had to go into the serv1\dev server
properties and click on the "Sql and Windows Authentication" option. Now I
can connect using my sql login
"Rich" wrote:

> Hello,
> Last night I loaded a 2nd instance (development instance) of sql server on
> my server machine (win2003). The first instance is the local server - ser
v1.
> The 2nd instance is serv1\dev. I loaded both instances using Windows
> authentication. Note: the first instance was loaded on the default port
of
> 1433. But for the 2nd instance, Setup assigned a default port of 0. I we
nt
> with port 0 and windows authentication. When I connect to serv1\dev in Qu
ery
> Analyzer through Windows authentication I connect OK. But I added a sql
> login to serv1\dev. I can't connect to serv1\dev from Query Analyzer usin
g
> my sql login. The error message says that the login failed because my log
in
> is not associated with a trusted sql server connection. So how do I
> associate my sql login with a trusted sql server connection?
> Note: On the original install I also added a sql login, and it works fine
> from the server computer and from remote workstations. Why did the serv1
> login work but not my new login on serv1\dev?
> Thanks,
> Rich

How to assess impact of changing database Compatibility level

I noticed that a database I am working with has a compatibility level set to SQL Server 2000. The instance is actually SQL Server 2005. I'm guessing that it was created like this because the database originally existed on 2000 and was created via backup/restore.

I'm trying to figure out if this needs to be changed and if so how to go about making the change in a non-disruptive manner. What features of 2005 are turned off as a reult of having a 2000 compatibility level?

You can easily change the compatibility level from 80 to 90 using the Properties dialog off the database name in Management Studio, on the Options page. Change the value and SQL Server will make the changes pretty rapidly.

Once you do this, any queries in your stored procedures that use the old outer join syntax (*= and =*) will stop working altogether. Make sure you aren't using that syntax in your application before making the changes. Also, any queries that reference non-existent columns will stop working as well. It's best not to do that anyway.

As far as benefits, you'll be able to run the wealth of dynamic management views (DMVs) against your database, which will allow you to use all the reports in Management Studio Microsoft has provided to evaluate space usage and performance information that are not available when using SQL 2000 compatibility mode.

Does that help?

|||

Yes, that helps.

Looks like a filter through source code will be necessary.

Does SQL Server have a utility to search the db objects (views/stored procs, etc) for this deprecated functionality?

And, if the switch doesn't go smoothly I assume the switch back is just as easy?

Thanks

|||

Check out the SQL Server 2005 Upgrade Advisor Tool it can scan for use of depricated functionality.

You can find the tool on the SQL Server Download Site.

Thanks

Michelle

|||

Hi Allen,

Thanks for the information. We will soon upgrade our databases to SQL 2005, but because of legacy application issues, we will be running in SQL 2000 compatibility mode, at least for the present. I want to use the XML Path syntax in order to concatenate column values, a new feature for SQL 2005. If we run in SQL 2000 compatibility mode, will this feature still be available?

Thanks very much,

Patricia

How to assess impact of changing database Compatibility level

I noticed that a database I am working with has a compatibility level set to SQL Server 2000. The instance is actually SQL Server 2005. I'm guessing that it was created like this because the database originally existed on 2000 and was created via backup/restore.

I'm trying to figure out if this needs to be changed and if so how to go about making the change in a non-disruptive manner. What features of 2005 are turned off as a reult of having a 2000 compatibility level?

You can easily change the compatibility level from 80 to 90 using the Properties dialog off the database name in Management Studio, on the Options page. Change the value and SQL Server will make the changes pretty rapidly.

Once you do this, any queries in your stored procedures that use the old outer join syntax (*= and =*) will stop working altogether. Make sure you aren't using that syntax in your application before making the changes. Also, any queries that reference non-existent columns will stop working as well. It's best not to do that anyway.

As far as benefits, you'll be able to run the wealth of dynamic management views (DMVs) against your database, which will allow you to use all the reports in Management Studio Microsoft has provided to evaluate space usage and performance information that are not available when using SQL 2000 compatibility mode.

Does that help?

|||

Yes, that helps.

Looks like a filter through source code will be necessary.

Does SQL Server have a utility to search the db objects (views/stored procs, etc) for this deprecated functionality?

And, if the switch doesn't go smoothly I assume the switch back is just as easy?

Thanks

|||

Check out the SQL Server 2005 Upgrade Advisor Tool it can scan for use of depricated functionality.

You can find the tool on the SQL Server Download Site.

Thanks

Michelle

|||

Hi Allen,

Thanks for the information. We will soon upgrade our databases to SQL 2005, but because of legacy application issues, we will be running in SQL 2000 compatibility mode, at least for the present. I want to use the XML Path syntax in order to concatenate column values, a new feature for SQL 2005. If we run in SQL 2000 compatibility mode, will this feature still be available?

Thanks very much,

Patricia

How to add Full Text Search to SQL2005

Hi,
We have a server with SQL2005 Standard Edition installed. We did not install
Full Text Search and want to add it to the existing default instance. The
attempt to install it says it is already installed when we know that it is
not. Is this a known bug?
Thanks
Chris
Seems you have to give the actual instance name, MSSQL for the default,
before the install will happen.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23TtGy7FIGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a server with SQL2005 Standard Edition installed. We did not
> install Full Text Search and want to add it to the existing default
> instance. The attempt to install it says it is already installed when we
> know that it is not. Is this a known bug?
> Thanks
> Chris
>

How to add Full Text Search to SQL2005

Hi,
We have a server with SQL2005 Standard Edition installed. We did not install
Full Text Search and want to add it to the existing default instance. The
attempt to install it says it is already installed when we know that it is
not. Is this a known bug?
Thanks
ChrisSeems you have to give the actual instance name, MSSQL for the default,
before the install will happen.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23TtGy7FIGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a server with SQL2005 Standard Edition installed. We did not
> install Full Text Search and want to add it to the existing default
> instance. The attempt to install it says it is already installed when we
> know that it is not. Is this a known bug?
> Thanks
> Chris
>

How to add a new instance to existing SQL Server 2000

Hi everybody,

I have an instance \VSdotNet for SQL Server 2000 on my machine. Now I want to create another instance called \NetSDK, so that the \NetSDK and \VSdotNet exist side by side.

Please let me know how I can do this.

Thanks
SunilPut the install disk in and follow the prompts ;)|||I do not have the disk since I downloaded the Net SDK in which SQL Server 2000 desktop is one component. But when I run the install program for SQL Server 2000, it says the SQL Server \NetSDk doesn't exist. Is there a tool that I can use to create a parallel instance.

Thanks
Sunil|||I've no idea what the rules are for "desktop" & "server" are but I'd uninstall destop and put Server on there twice - providing the licensing allows.

How to add a new .mdf with ONLY sql authentication in SQL Express?

Hi all.

I am going around in circles all day on this.

I have a clean new install of win XP SP2, VS 2005 Pro RTM,
and a NAMED instance of Sql Express with mixed authentication mode specified at setup.
The sa pw is 123456

I would have liked just SQL authentication but no such option.

I am trying to add a new mdf file. If I just use the "Add New Item" in VS web app project it just adds a new .mdf file with windows authentication tied to the current profile I am logged in the os with. I am trying to avoid that as this projects is to be zipped and downloaded as a sample app. I am also trying to avoid embedding an instance of SSE as the recipients of the zip file already have SSE running iwth thei VS IDE.

So most logical is to create the file with SQL authentication.
I have to do it by the Add Connection GUI

and for data source: "Microsoft SQL Server Database File (SqlClient) "

for data file name: C:\WebSites\Website1\App_Data\MyDatabase1.mdf (not yet existing file)

Use SQl server authentication:

user: sa, password: 123456 (the ones specified at set up of the SSE)

I keep getting

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.

I keep getting the same error no matter what username I use or password, the current login, etc. It just does not let me use sql authentication.

Please help anyone. This is a nightmare.

"Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection."

This seems that you didn′t activate Mmixed Authentication, its an error you are getting if you only specified the Windows Authentication. Try setting the authentication mode for the express instance:

<snip>
Another way to change the security mode after installation is to stop
SQL Server and set the appropriate registry key for your installation:

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode

to 2 for mixed-mode or 1 for integrated. (Integrated is the default
setup for the SQL Server 2000 Data Engine.)
</snip>

HTH, Jens Suessmeyer.

|||

Thanks Jens,

1. I made sure during the install I selected mixed mode.

2. This article essentially says the same. However, I cannot find in the registry any "LoginMode" subkey for either the default SQLEXPRESS, or MySSE named instance.

Actually I did a complete search and there is no Key, Value or Data that contains "LoginMode" in the whole registry.

|||

You can interrogate the database engine with the CTP SQL Express Mamnagement Suite downloadable from Microsoft.

Click select the database engine..... right click and select export

You'll get a file like this:

<?xml version="1.0" encoding="utf-8"?>
<Export serverType="8c91a03d-f9b4-46c0-a305-b5dcc79ff907">
<ServerType id="8c91a03d-f9b4-46c0-a305-b5dcc79ff907" name="Database Engine">
<Server name="Shhhh\sqlexpress" description="Local instance - 'bliss\sqlexpress'">
<ConnectionInformation>
<ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</ServerType>
<ServerName>Shhhh\sqlexpress</ServerName>
<AuthenticationType>0</AuthenticationType>
<UserName />
<Password />
<AdvancedOptions />
</ConnectionInformation>
</Server>
</ServerType>
</Export>

You'll see that ny authentication type is 0 which is windows authentification.

I ust went through this and there is a way to attach this programattically.

Use the Sql Management Classes in your code....

Create an XP UserGroup and user using the SMO classes. Then create the same user group in and user in the database and Attach it. It never fails and yes... I know how frustrating this is. I lost a lost of sleep over this this weekend.

Check out the thread in the first forum of this board for more information.

Renee

|||

Thanks Rene,

I learned something. I don't need the SSMSE becuase I have the full version installed too.

However I still can't fix the problem. This is what I have done so far:

I exported a .regservr and the xml looks just like you show. authetication 0. So I went back and looked in the registry again. I found a LoginMode subkey but in a different key:

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

It didn't quite make sense, because I am trying to change a NAMED instance. As expected it did not work. The exported .regsrvr still shows the same authentication mode 0.

I am scared to start adding registry keys that do not even exist.

The nightmare continues.

|||

Carl,

You are doing this programmatically?

|||

I meant to ask you about a link to the thread you mentioned.

What do you mean programmatically?

Create a c# app that could access and modify these registry keys in code? in which case NO.|||

Not true.... I spent the weekend working on this and a friend really helped my understand. You can do excellent work with SSE.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=223272&SiteID=1&PageID=0

post an email adress and I will send you the solution.

|||

http://msdn2.microsoft.com/microsoft.sqlserver.management.smo.server.attachdatabase.aspx

http://msdn2.microsoft.com/ms160723.aspx

|||

oh yeah. . . you can't do SQL authentication only.

its either windows or mixed mode

How to add a named instance of sql server to EM?

I got an app that installed a named instance of MSDE instead of including
its databases in my existing SQl server instance. I would like to be able to
manage the databases and view the table structures of this app in Enterprise
manager. How can I include that named instance and its tables etc.. in my
SQL server Enterprise manager?
Thanks for any help,
BobBob,
Try registering the SQL Server instance using computername\instancename.
HTH
Jerry
"Bob" <bdufour@.sgiims.com> wrote in message
news:%23TdlkDDyFHA.2500@.TK2MSFTNGP10.phx.gbl...
>I got an app that installed a named instance of MSDE instead of including
>its databases in my existing SQl server instance. I would like to be able
>to manage the databases and view the table structures of this app in
>Enterprise manager. How can I include that named instance and its tables
>etc.. in my SQL server Enterprise manager?
> Thanks for any help,
> Bob
>|||Thanks but does not work, I get a does not exist or acces denied when I try
to register it. I'm set up to use mixed mode authentication and use the
system account to login and I am sa. but I can't seem to access that
instance of Sql server.
Any othetr way anyone can think of?
Thanks for your help.
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u9W30aDyFHA.596@.TK2MSFTNGP12.phx.gbl...
> Bob,
> Try registering the SQL Server instance using computername\instancename.
> HTH
> Jerry
> "Bob" <bdufour@.sgiims.com> wrote in message
> news:%23TdlkDDyFHA.2500@.TK2MSFTNGP10.phx.gbl...
>|||Are you connecting across a network? Did you try to log onto
the box and connect?
Check the error logs from the MSDE instance from when it
started up to see what protocol it is listening on - if it's
just shared memory, you can't connect to it over a network.
In that case, you need to enable TCP/IP.
-Sue
On Mon, 3 Oct 2005 13:39:08 -0400, "Bob"
<bdufour@.sgiims.com> wrote:

>Thanks but does not work, I get a does not exist or acces denied when I try
>to register it. I'm set up to use mixed mode authentication and use the
>system account to login and I am sa. but I can't seem to access that
>instance of Sql server.
>Any othetr way anyone can think of?
>Thanks for your help.
>Bob
>"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>news:u9W30aDyFHA.596@.TK2MSFTNGP12.phx.gbl...
>

How to add a named instance of sql server to EM?

I got an app that installed a named instance of MSDE instead of including
its databases in my existing SQl server instance. I would like to be able to
manage the databases and view the table structures of this app in Enterprise
manager. How can I include that named instance and its tables etc.. in my
SQL server Enterprise manager?
Thanks for any help,
BobBob,
Try registering the SQL Server instance using computername\instancename.
HTH
Jerry
"Bob" <bdufour@.sgiims.com> wrote in message
news:%23TdlkDDyFHA.2500@.TK2MSFTNGP10.phx.gbl...
>I got an app that installed a named instance of MSDE instead of including
>its databases in my existing SQl server instance. I would like to be able
>to manage the databases and view the table structures of this app in
>Enterprise manager. How can I include that named instance and its tables
>etc.. in my SQL server Enterprise manager?
> Thanks for any help,
> Bob
>|||Thanks but does not work, I get a does not exist or acces denied when I try
to register it. I'm set up to use mixed mode authentication and use the
system account to login and I am sa. but I can't seem to access that
instance of Sql server.
Any othetr way anyone can think of?
Thanks for your help.
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u9W30aDyFHA.596@.TK2MSFTNGP12.phx.gbl...
> Bob,
> Try registering the SQL Server instance using computername\instancename.
> HTH
> Jerry
> "Bob" <bdufour@.sgiims.com> wrote in message
> news:%23TdlkDDyFHA.2500@.TK2MSFTNGP10.phx.gbl...
>>I got an app that installed a named instance of MSDE instead of including
>>its databases in my existing SQl server instance. I would like to be able
>>to manage the databases and view the table structures of this app in
>>Enterprise manager. How can I include that named instance and its tables
>>etc.. in my SQL server Enterprise manager?
>> Thanks for any help,
>> Bob
>>
>|||Are you connecting across a network? Did you try to log onto
the box and connect?
Check the error logs from the MSDE instance from when it
started up to see what protocol it is listening on - if it's
just shared memory, you can't connect to it over a network.
In that case, you need to enable TCP/IP.
-Sue
On Mon, 3 Oct 2005 13:39:08 -0400, "Bob"
<bdufour@.sgiims.com> wrote:
>Thanks but does not work, I get a does not exist or acces denied when I try
>to register it. I'm set up to use mixed mode authentication and use the
>system account to login and I am sa. but I can't seem to access that
>instance of Sql server.
>Any othetr way anyone can think of?
>Thanks for your help.
>Bob
>"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>news:u9W30aDyFHA.596@.TK2MSFTNGP12.phx.gbl...
>> Bob,
>> Try registering the SQL Server instance using computername\instancename.
>> HTH
>> Jerry
>> "Bob" <bdufour@.sgiims.com> wrote in message
>> news:%23TdlkDDyFHA.2500@.TK2MSFTNGP10.phx.gbl...
>>I got an app that installed a named instance of MSDE instead of including
>>its databases in my existing SQl server instance. I would like to be able
>>to manage the databases and view the table structures of this app in
>>Enterprise manager. How can I include that named instance and its tables
>>etc.. in my SQL server Enterprise manager?
>> Thanks for any help,
>> Bob
>>
>>
>

How to add a named instance of sql server to EM?

I got an app that installed a named instance of MSDE instead of including
its databases in my existing SQl server instance. I would like to be able to
manage the databases and view the table structures of this app in Enterprise
manager. How can I include that named instance and its tables etc.. in my
SQL server Enterprise manager?
Thanks for any help,
Bob
Bob,
Try registering the SQL Server instance using computername\instancename.
HTH
Jerry
"Bob" <bdufour@.sgiims.com> wrote in message
news:%23TdlkDDyFHA.2500@.TK2MSFTNGP10.phx.gbl...
>I got an app that installed a named instance of MSDE instead of including
>its databases in my existing SQl server instance. I would like to be able
>to manage the databases and view the table structures of this app in
>Enterprise manager. How can I include that named instance and its tables
>etc.. in my SQL server Enterprise manager?
> Thanks for any help,
> Bob
>
|||Thanks but does not work, I get a does not exist or acces denied when I try
to register it. I'm set up to use mixed mode authentication and use the
system account to login and I am sa. but I can't seem to access that
instance of Sql server.
Any othetr way anyone can think of?
Thanks for your help.
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u9W30aDyFHA.596@.TK2MSFTNGP12.phx.gbl...
> Bob,
> Try registering the SQL Server instance using computername\instancename.
> HTH
> Jerry
> "Bob" <bdufour@.sgiims.com> wrote in message
> news:%23TdlkDDyFHA.2500@.TK2MSFTNGP10.phx.gbl...
>
|||Are you connecting across a network? Did you try to log onto
the box and connect?
Check the error logs from the MSDE instance from when it
started up to see what protocol it is listening on - if it's
just shared memory, you can't connect to it over a network.
In that case, you need to enable TCP/IP.
-Sue
On Mon, 3 Oct 2005 13:39:08 -0400, "Bob"
<bdufour@.sgiims.com> wrote:

>Thanks but does not work, I get a does not exist or acces denied when I try
>to register it. I'm set up to use mixed mode authentication and use the
>system account to login and I am sa. but I can't seem to access that
>instance of Sql server.
>Any othetr way anyone can think of?
>Thanks for your help.
>Bob
>"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>news:u9W30aDyFHA.596@.TK2MSFTNGP12.phx.gbl...
>