Authentication in Management Studio

Hi,

I'm attempting to connect to a SQL Server 2005 instance on a Windows 2003 server remotely with Management Studio on a Windows XP box. SQL Server is set to use Windows Authentication.

If I select Windows Authentication as the authentication method in Management Studio, the 'user name' field is greyed-out and locked to my current XP user name, which is no good for connecting to the server.

Why is this happening and how can I change the user name and password to that of an actual account on the server?

TIA,

CraigyBoop

You can′t switch that for integrated security, as "integrated" security uses the logged on user. The field Username is only valid and accessible if you choose SQL Server Authentication. There are way to impersonate yourself with a Windows credential, but AFAIK these are only programmatically.

HTH, Jens Suessmeyer.|||The account name is picked up from what you have used to login to Windows. This is the idea about "trusted connections", SQL Server trusts that windows has authenticated the login. Start SSMS in another windows account (Windows "Run As" feature) if you want to authenticate differently. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:67a6dbd1-6f2c-4fd9-93a9-c93d090ea724@.discussions.microsoft.com...
> Hi, >
> I'm attempting to connect to a SQL Server 2005 instance on a Windows
> 2003 server remotely with Management Studio on a Windows XP box. SQL
> Server is set to use Windows Authentication. >
> If I select Windows Authentication as the authentication method in
> Management Studio, the 'user name' field is greyed-out and locked to my
> current XP user name, which is no good for connecting to the server. >
> Why is this happening and how can I change the user name and password to
> that of an actual account on the server? >
> TIA, >
> CraigyBoop >
>|||

I don't understand why that counts as a "Trusted Connection". What's untrusted about being able to log onto a SQL Server with a different Windows account from your current one. Presumably SQL Server can authenticate your Windows credentials if you are using credentials within the same domain?

I too am frustrated by not being able to connect as a different user. Any chance you can explain the reasoning behind this?

Thanks,

Colin

|||I agree this is lame (to use a technical term). If you rely on the client for security, which is what this is doing, you're not secure.
When I'm working remotely I have to use Aqua Data Studio to connect to our db so I can work. The ironic thing about that even though is it's written in Java and a totally non-native client app, it supports Intelli-sense, and will prompt for network credentials. Of course it's java-app so it looks all crappy and acts weird or else I might consider using it full time.
The run-as idea is not bad. I'll give that a try next time.
|||Hi,

actually this is not right, there is not just passed a username in combination with the domain, the user is authenticated and the user token is passed to the Server, thats a different thing than just the user name. So SQL Server trusts the logon server not the client that the user was validated and authenticated.

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de

Authentication in Management Studio

Hi,

I'm attempting to connect to a SQL Server 2005 instance on a Windows 2003 server remotely with Management Studio on a Windows XP box. SQL Server is set to use Windows Authentication.

If I select Windows Authentication as the authentication method in Management Studio, the 'user name' field is greyed-out and locked to my current XP user name, which is no good for connecting to the server.

Why is this happening and how can I change the user name and password to that of an actual account on the server?

TIA,

CraigyBoop

You can′t switch that for integrated security, as "integrated" security uses the logged on user. The field Username is only valid and accessible if you choose SQL Server Authentication. There are way to impersonate yourself with a Windows credential, but AFAIK these are only programmatically.

HTH, Jens Suessmeyer.|||The account name is picked up from what you have used to login to Windows. This is the idea about "trusted connections", SQL Server trusts that windows has authenticated the login. Start SSMS in another windows account (Windows "Run As" feature) if you want to authenticate differently. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:67a6dbd1-6f2c-4fd9-93a9-c93d090ea724@.discussions.microsoft.com...
> Hi, >
> I'm attempting to connect to a SQL Server 2005 instance on a Windows
> 2003 server remotely with Management Studio on a Windows XP box. SQL
> Server is set to use Windows Authentication. >
> If I select Windows Authentication as the authentication method in
> Management Studio, the 'user name' field is greyed-out and locked to my
> current XP user name, which is no good for connecting to the server. >
> Why is this happening and how can I change the user name and password to
> that of an actual account on the server? >
> TIA, >
> CraigyBoop >
>|||

I don't understand why that counts as a "Trusted Connection". What's untrusted about being able to log onto a SQL Server with a different Windows account from your current one. Presumably SQL Server can authenticate your Windows credentials if you are using credentials within the same domain?

I too am frustrated by not being able to connect as a different user. Any chance you can explain the reasoning behind this?

Thanks,

Colin

|||I agree this is lame (to use a technical term). If you rely on the client for security, which is what this is doing, you're not secure.
When I'm working remotely I have to use Aqua Data Studio to connect to our db so I can work. The ironic thing about that even though is it's written in Java and a totally non-native client app, it supports Intelli-sense, and will prompt for network credentials. Of course it's java-app so it looks all crappy and acts weird or else I might consider using it full time.
The run-as idea is not bad. I'll give that a try next time.
|||Hi,

actually this is not right, there is not just passed a username in combination with the domain, the user is authenticated and the user token is passed to the Server, thats a different thing than just the user name. So SQL Server trusts the logon server not the client that the user was validated and authenticated.

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de
sql

Authentication in Management Studio

Hi,

I'm attempting to connect to a SQL Server 2005 instance on a Windows 2003 server remotely with Management Studio on a Windows XP box. SQL Server is set to use Windows Authentication.

If I select Windows Authentication as the authentication method in Management Studio, the 'user name' field is greyed-out and locked to my current XP user name, which is no good for connecting to the server.

Why is this happening and how can I change the user name and password to that of an actual account on the server?

TIA,

CraigyBoop

You can′t switch that for integrated security, as "integrated" security uses the logged on user. The field Username is only valid and accessible if you choose SQL Server Authentication. There are way to impersonate yourself with a Windows credential, but AFAIK these are only programmatically.

HTH, Jens Suessmeyer.|||The account name is picked up from what you have used to login to Windows. This is the idea about

"trusted connections", SQL Server trusts that windows has authenticated the login. Start SSMS in

another windows account (Windows "Run As" feature) if you want to authenticate differently.

--

Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://www.solidqualitylearning.com/

Blog: http://solidqualitylearning.com/blogs/tibor/

wrote in message

news:67a6dbd1-6f2c-4fd9-93a9-c93d090ea724@.discussions.microsoft.com...

> Hi,

>

> I'm attempting to connect to a SQL Server 2005 instance on a Windows

> 2003 server remotely with Management Studio on a Windows XP box. SQL

> Server is set to use Windows Authentication.

>

> If I select Windows Authentication as the authentication method in

> Management Studio, the 'user name' field is greyed-out and locked to my

> current XP user name, which is no good for connecting to the server.

>

> Why is this happening and how can I change the user name and password to

> that of an actual account on the server?

>

> TIA,

>

> CraigyBoop

>

>|||

I don't understand why that counts as a "Trusted Connection". What's untrusted about being able to log onto a SQL Server with a different Windows account from your current one. Presumably SQL Server can authenticate your Windows credentials if you are using credentials within the same domain?

I too am frustrated by not being able to connect as a different user. Any chance you can explain the reasoning behind this?

Thanks,

Colin

|||I agree this is lame (to use a technical term). If you rely on the client for security, which is what this is doing, you're not secure.
When I'm working remotely I have to use Aqua Data Studio to connect to our db so I can work. The ironic thing about that even though is it's written in Java and a totally non-native client app, it supports Intelli-sense, and will prompt for network credentials. Of course it's java-app so it looks all crappy and acts weird or else I might consider using it full time.
The run-as idea is not bad. I'll give that a try next time.|||Hi,

actually this is not right, there is not just passed a username in combination with the domain, the user is authenticated and the user token is passed to the Server, thats a different thing than just the user name. So SQL Server trusts the logon server not the client that the user was validated and authenticated.

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de

Attempting to transpose/pivot columns to rows... I think.

I have something of a challenge for you folks (at least it's very challenging for me).

I have a table that has data that looks like this:

DateOrderNum_WorkDay

2006-06-1__AA1

2006-06-1__AB1

2006-06-2__BA2

2006-06-2__BB2

2006-06-2__BC2

2006-06-5__CA3

2006-06-5__CB3

etc.

So, there are dates that orders happened and each date is marked as the 1, 2, 3, etc. business day of the month.

What I am trying to write is a SQL statement that will display results thus:

Day1Day2Day3_etc.

AA__BA_CAetc.

AB__BB_CBetc.

Is this making any sense to anyone? Basically, I need to turn the WorkDay rows into columns and display all the info for each WorkDay under them.

I have a feeling this isn't hard, but I am fairly new to writing SQL so any advice would be pure gold to me. Thanks!

2 questions.

1 - What version of SQL Server are you using? Is it 2005?

2- Is the set of Work days dynamic, or do you know the specific work days when you are writing the query. More specifically, do the columns in the result need to be generated dynamically from the data in the table?

|||

Ryan,

Thanks for the quick reply! Here are the answers to your questions:

1. I'm using SQL Server 2005 (and I just found the PIVOT command which is new in 2005 apparently).

2. The set of workdays is dynamic. Depending on the size of the month and holidays there could be as few as 20 or as many as 23. And if I understand your second question then yes the columns will need to be generated dynammically. The data in the table will be changing daily and this SQL statement is generating a grid view on a web page.

Thanks again!

|||

Unfortunately, the dynamic aspect of your query makes it pretty complicated.

The PIVOT function is nice, but it requires that you know the resulting columns when you build the SQL. So, what you need to is create a stored proc that dynamically builds the PIVOT sql syntax by looping through the data in your table.

I know of a couple of good articles with examples of doing this (all-be-it using the old SQL 2000 SQL pivot logic).

A good article on simple PIVOTS. http://www.aspfaq.com/show.asp?id=2462

That article has a link to 2 dynamic pivot articles. I think the first is the best for you. http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

If I were you, I'd consider re-writing this for the new PIVOT function, and perhaps someone here already has. If this doesn't work, I could probably find some time to do this for you. Let me know. But, I think just using the old version described in this article should work.

|||

Thanks again, Ryan! You have a wealth of knowledge.

I am doing this for a project that needs to be ready this Wednesday, so I may not have enough time to construct this myself by then. However, I am loathe to have you do my work for me. So, I will probably do a quick and dirty view on the data and over the next week or so wrap my mind around your links and other advice. If I do need further advice I will post to this thread and hope you check back now and then.

Thanks for everything!

|||

Hi there Ryan,

I've seen you've gave a solution in case of SQL Server 2005. Do you have a solution for 2000?

Thanks in advance

|||You can have the best example here:
http://orafaq.com/node/1871|||

Code Snippet

If you use SQL Server 2005,

use tempdb

go

create table data (

[date] datetime ,

[ordernum] varchar(100) ,

[workday] int

);

go

insert into data values('2006-06-1','AA','1');

insert into data values('2006-06-1','AB','1');

insert into data values('2006-06-2','BA','2');

insert into data values('2006-06-2','BB','2');

insert into data values('2006-06-2','BC','2');

insert into data values('2006-06-5','CA','3');

insert into data values('2006-06-5','CB','3');

go

;with cte

as

(

select *, row_number() over(partition by date order by ordernum) rid from data

)

select

isnull([1],'') as day1

, isnull([2],'') as day2

, isnull([3],'') as day3

, isnull([4],'') as day4

, isnull([5],'') as day5

, isnull([6],'') as day6

, isnull([7],'') as day7

from

(

select

workday

, rid

, ordernum

from

cte

) as data

pivot

(

max(ordernum)

for workday in

([1],[2],[3],[4],[5],[6],[7] /*.....,[n]*/)

)as pvt

Attempting to transpose/pivot columns to rows... I think.

I have something of a challenge for you folks (at least it's very challenging for me).

I have a table that has data that looks like this:

DateOrderNum_WorkDay

2006-06-1__AA1

2006-06-1__AB1

2006-06-2__BA2

2006-06-2__BB2

2006-06-2__BC2

2006-06-5__CA3

2006-06-5__CB3

etc.

So, there are dates that orders happened and each date is marked as the 1, 2, 3, etc. business day of the month.

What I am trying to write is a SQL statement that will display results thus:

Day1Day2Day3_etc.

AA__BA_CAetc.

AB__BB_CBetc.

Is this making any sense to anyone? Basically, I need to turn the WorkDay rows into columns and display all the info for each WorkDay under them.

I have a feeling this isn't hard, but I am fairly new to writing SQL so any advice would be pure gold to me. Thanks!

2 questions.

1 - What version of SQL Server are you using? Is it 2005?

2- Is the set of Work days dynamic, or do you know the specific work days when you are writing the query. More specifically, do the columns in the result need to be generated dynamically from the data in the table?

|||

Ryan,

Thanks for the quick reply! Here are the answers to your questions:

1. I'm using SQL Server 2005 (and I just found the PIVOT command which is new in 2005 apparently).

2. The set of workdays is dynamic. Depending on the size of the month and holidays there could be as few as 20 or as many as 23. And if I understand your second question then yes the columns will need to be generated dynammically. The data in the table will be changing daily and this SQL statement is generating a grid view on a web page.

Thanks again!

|||

Unfortunately, the dynamic aspect of your query makes it pretty complicated.

The PIVOT function is nice, but it requires that you know the resulting columns when you build the SQL. So, what you need to is create a stored proc that dynamically builds the PIVOT sql syntax by looping through the data in your table.

I know of a couple of good articles with examples of doing this (all-be-it using the old SQL 2000 SQL pivot logic).

A good article on simple PIVOTS. http://www.aspfaq.com/show.asp?id=2462

That article has a link to 2 dynamic pivot articles. I think the first is the best for you. http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

If I were you, I'd consider re-writing this for the new PIVOT function, and perhaps someone here already has. If this doesn't work, I could probably find some time to do this for you. Let me know. But, I think just using the old version described in this article should work.

|||

Thanks again, Ryan! You have a wealth of knowledge.

I am doing this for a project that needs to be ready this Wednesday, so I may not have enough time to construct this myself by then. However, I am loathe to have you do my work for me. So, I will probably do a quick and dirty view on the data and over the next week or so wrap my mind around your links and other advice. If I do need further advice I will post to this thread and hope you check back now and then.

Thanks for everything!

Attempting to restore a db from a network drive and use the 'with move' statement

Attempting to restore a db from a network drive and need to move the
log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
Here is the script that I am using for this procedure
===================================== USE MASTER
GO
RESTORE FILELISTONLY
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
RESTORE LOG Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
-- WITH NORECOVERY
RESTORE DATABASE Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH NORECOVERY
=====================================
Here are the error(s) that I am receiving
===================================== Server: Msg 913, Level 16, State 8, Line 3
Could not find database ID 65535. Database may not be activated yet or
may be in transition.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
Server: Msg 5105, Level 16, State 2, Line 7
Device activation error. The physical file name 'C:\Program
Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
incorrect.
Server: Msg 3156, Level 16, State 1, Line 7
File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.war_wheelan@.yahoo.com wrote:
> Attempting to restore a db from a network drive and need to move the
> log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
> AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
> Here is the script that I am using for this procedure
> =====================================> USE MASTER
> GO
> RESTORE FILELISTONLY
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> RESTORE LOG Indii
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
> -- WITH NORECOVERY
> RESTORE DATABASE Indii
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> WITH NORECOVERY
> =====================================> Here are the error(s) that I am receiving
> =====================================> Server: Msg 913, Level 16, State 8, Line 3
> Could not find database ID 65535. Database may not be activated yet or
> may be in transition.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE LOG is terminating abnormally.
> Server: Msg 5105, Level 16, State 2, Line 7
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
> incorrect.
> Server: Msg 3156, Level 16, State 1, Line 7
> File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
> a valid location for the file.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE DATABASE is terminating abnormally.
>
It looks like you're trying to restore the log before the database has
been restored. Restore the database first, THEN the log.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
The reason that I attempted to restore the log file first is because I
receive the same error either way.
Tracy McKibben wrote:
> war_wheelan@.yahoo.com wrote:
> > Attempting to restore a db from a network drive and need to move the
> > log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
> > AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
> >
> > Here is the script that I am using for this procedure
> > =====================================> > USE MASTER
> > GO
> > RESTORE FILELISTONLY
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > RESTORE LOG Indii
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
> > -- WITH NORECOVERY
> > RESTORE DATABASE Indii
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > WITH NORECOVERY
> > =====================================> >
> > Here are the error(s) that I am receiving
> > =====================================> > Server: Msg 913, Level 16, State 8, Line 3
> > Could not find database ID 65535. Database may not be activated yet or
> > may be in transition.
> > Server: Msg 3013, Level 16, State 1, Line 3
> > RESTORE LOG is terminating abnormally.
> > Server: Msg 5105, Level 16, State 2, Line 7
> > Device activation error. The physical file name 'C:\Program
> > Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
> > incorrect.
> > Server: Msg 3156, Level 16, State 1, Line 7
> > File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
> > a valid location for the file.
> > Server: Msg 3013, Level 16, State 1, Line 7
> > RESTORE DATABASE is terminating abnormally.
> >
> It looks like you're trying to restore the log before the database has
> been restored. Restore the database first, THEN the log.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Attempting to restore a db from a network drive and use the 'with move' statement

Attempting to restore a db from a network drive and need to move the
log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
Here is the script that I am using for this procedure
=====================================
USE MASTER
GO
RESTORE FILELISTONLY
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
RESTORE LOG Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$INDII_NY2_PROD\Data\Indii_L
og.LDF'
-- WITH NORECOVERY
RESTORE DATABASE Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH NORECOVERY
=====================================
Here are the error(s) that I am receiving
=====================================
Server: Msg 913, Level 16, State 8, Line 3
Could not find database ID 65535. Database may not be activated yet or
may be in transition.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
Server: Msg 5105, Level 16, State 2, Line 7
Device activation error. The physical file name 'C:\Program
Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
incorrect.
Server: Msg 3156, Level 16, State 1, Line 7
File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.Tracy,
The reason that I attempted to restore the log file first is because I
receive the same error either way.
Tracy McKibben wrote:
> war_wheelan@.yahoo.com wrote:
> It looks like you're trying to restore the log before the database has
> been restored. Restore the database first, THEN the log.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

attempting to create a linked server to MySQL

We attempted to create a link server between an MSDE database and MySQL 4.1
database using ODBC. However, whenever we attempt to look at the tables
associated with the link we are greeted with the message: Could not create
an instance of OLE DB provider 'MSDASQL'. OLD DB error trace [Non-interface
error: Cocreate of DSO for MSDASQL returned 0x800700005].
When I searched the web for this message, I learned that it is not unusual
to encounter it, but have not been able to find a way to get past it. The
version of MDAC appears to be consistent with the Microsoft table relating
version and Windows version (2000).
Any direction or source of specific aids would be appreciated.
We have had some success, but different problem which may be essentially the
same.
On a different machine which has MSDE , loaded a MySQL 4.1 database. When
adding the ODBC/Connector used the FTP address of this machine in the block
for Server. tested the connection succesfully.
Then used query analyzer to EXEC sp_addlinkedserver with
New LinkedServer Name,
string MYSQL 4.1,
string MSDASQL,
the name of the ODBC/Connector from first step,
Null,
string Driver={MySQL ODBC 3.51 Driver}
After execution of this I started up Enterprise Mgr and could click of the
Linked Server name, and then the tables Icon and a list of all the tables in
the MySql database was visible.
I went back to the query analyzer and executed a select statement and saw
data from field1 in tbl1 of the Mysql databas3e when I executed the
statemnet:
Select * from OPENQUERY( new linked servername, 'Select field1 from tbl1')
End of success.
i started up an Access ADP program and attempted to add external data using
the Linked Table wizard. After selecting tbl1 it created an odc file, which
when I attempted to open it was greeted with the error message: This
DataSource contains no visible tables. ...AAAH ...the next problem
Thanks to those who sent on comments. Comments and
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
[Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>
|||Not an expert on this but I believe that linking servers requires OLEDB.
Can't be done with an ODBC driver. You should be able to fine an OLEDB
driver out there to make this work.
Andrew Robinson
www.binaryocean.com
www.bellinghamdotnet.org
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
> 4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
> create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
> [Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>

attempting to create a linked server to MySQL

We attempted to create a link server between an MSDE database and MySQL 4.1
database using ODBC. However, whenever we attempt to look at the tables
associated with the link we are greeted with the message: Could not create
an instance of OLE DB provider 'MSDASQL'. OLD DB error trace [Non-interface
error: Cocreate of DSO for MSDASQL returned 0x800700005].
When I searched the web for this message, I learned that it is not unusual
to encounter it, but have not been able to find a way to get past it. The
version of MDAC appears to be consistent with the Microsoft table relating
version and Windows version (2000).
Any direction or source of specific aids would be appreciated.
We have had some success, but different problem which may be essentially the
same.
On a different machine which has MSDE , loaded a MySQL 4.1 database. When
adding the ODBC/Connector used the FTP address of this machine in the block
for Server. tested the connection succesfully.
Then used query analyzer to EXEC sp_addlinkedserver with
New LinkedServer Name,
string MYSQL 4.1,
string MSDASQL,
the name of the ODBC/Connector from first step,
Null,
string Driver={MySQL ODBC 3.51 Driver}
After execution of this I started up Enterprise Mgr and could click of the
Linked Server name, and then the tables Icon and a list of all the tables in
the MySql database was visible.
I went back to the query analyzer and executed a select statement and saw
data from field1 in tbl1 of the Mysql databas3e when I executed the
statemnet:
Select * from OPENQUERY( new linked servername, 'Select field1 from tbl1')
End of success.
i started up an Access ADP program and attempted to add external data using
the Linked Table wizard. After selecting tbl1 it created an odc file, which
when I attempted to open it was greeted with the error message: This
DataSource contains no visible tables. ...AAAH ...the next problem
Thanks to those who sent on comments. Comments and
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
[Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>
|||Not an expert on this but I believe that linking servers requires OLEDB.
Can't be done with an ODBC driver. You should be able to fine an OLEDB
driver out there to make this work.
Andrew Robinson
www.binaryocean.com
www.bellinghamdotnet.org
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
> 4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
> create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
> [Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>

attempting to create a linked server to MySQL

We attempted to create a link server between an MSDE database and MySQL 4.1
database using ODBC. However, whenever we attempt to look at the tables
associated with the link we are greeted with the message: Could not create
an instance of OLE DB provider 'MSDASQL'. OLD DB error trace [Non-interface
error: Cocreate of DSO for MSDASQL returned 0x800700005].
When I searched the web for this message, I learned that it is not unusual
to encounter it, but have not been able to find a way to get past it. The
version of MDAC appears to be consistent with the Microsoft table relating
version and Windows version (2000).
Any direction or source of specific aids would be appreciated.
We have had some success, but different problem which may be essentially the
same.
On a different machine which has MSDE , loaded a MySQL 4.1 database. When
adding the ODBC/Connector used the FTP address of this machine in the block
for Server. tested the connection succesfully.
Then used query analyzer to EXEC sp_addlinkedserver with
New LinkedServer Name,
string MYSQL 4.1,
string MSDASQL,
the name of the ODBC/Connector from first step,
Null,
string Driver={MySQL ODBC 3.51 Driver}
After execution of this I started up Enterprise Mgr and could click of the
Linked Server name, and then the tables Icon and a list of all the tables in
the MySql database was visible.
I went back to the query analyzer and executed a select statement and saw
data from field1 in tbl1 of the Mysql databas3e when I executed the
statemnet:
Select * from OPENQUERY( new linked servername, 'Select field1 from tbl1')
End of success.
i started up an Access ADP program and attempted to add external data using
the Linked Table wizard. After selecting tbl1 it created an odc file, which
when I attempted to open it was greeted with the error message: This
DataSource contains no visible tables. ...AAAH ...the next problem
Thanks to those who sent on comments. Comments and
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
[Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>
|||Not an expert on this but I believe that linking servers requires OLEDB.
Can't be done with an ODBC driver. You should be able to fine an OLEDB
driver out there to make this work.
Andrew Robinson
www.binaryocean.com
www.bellinghamdotnet.org
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
> 4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
> create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
> [Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>

attempting to create a linked server to MySQL

We attempted to create a link server between an MSDE database and mysql 4.1
database using ODBC. However, whenever we attempt to look at the tables
associated with the link we are greeted with the message: Could not create
an instance of OLE DB provider 'MSDASQL'. OLD DB error trace [Non-interf
ace
error: Cocreate of DSO for MSDASQL returned 0x800700005].
When I searched the web for this message, I learned that it is not unusual
to encounter it, but have not been able to find a way to get past it. The
version of MDAC appears to be consistent with the Microsoft table relating
version and Windows version (2000).
Any direction or source of specific aids would be appreciated.We have had some success, but different problem which may be essentially the
same.
On a different machine which has MSDE , loaded a mysql 4.1 database. When
adding the ODBC/Connector used the FTP address of this machine in the block
for Server. tested the connection succesfully.
Then used query analyzer to EXEC sp_addlinkedserver with
New LinkedServer Name,
string mysql 4.1,
string MSDASQL,
the name of the ODBC/Connector from first step,
Null,
string Driver={MySQL ODBC 3.51 Driver}
After execution of this I started up Enterprise Mgr and could click of the
Linked Server name, and then the tables Icon and a list of all the tables in
the mysql database was visible.
I went back to the query analyzer and executed a select statement and saw
data from field1 in tbl1 of the mysql databas3e when I executed the
statemnet:
Select * from OPENQUERY( new linked servername, 'Select field1 from tbl1')
End of success.
i started up an Access ADP program and attempted to add external data using
the Linked Table wizard. After selecting tbl1 it created an odc file, which
when I attempted to open it was greeted with the error message: This
DataSource contains no visible tables. ...AAAH ...the next problem
Thanks to those who sent on comments. Comments and
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
[Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>|||Not an expert on this but I believe that linking servers requires OLEDB.
Can't be done with an ODBC driver. You should be able to fine an OLEDB
driver out there to make this work.
Andrew Robinson
www.binaryocean.com
www.bellinghamdotnet.org
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
> 4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
> create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
> [Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>

Attempting to Connect to SQLServer 2k

This is the code I'm using on the web page located on the server:

Dim strConn as String
Dim conn as SQLConnection
strConn="server=localhost;Trusted_Connection=Yes;database=CIS"

conn=New SQLConnection(strConn)
conn.Open()

and the error messgae I receive is: Login failed for user 'MSP00427\ASPNET'.

msp00427 is the name of the server
= = = = = = = = =

I have an MSAccess application that accesses the same database as follows with no trouble.

glblConnectString = "Driver=SQL Server;Server=msp00427;Database=CIS;Trusted_Connection=Yes"

Set cnn = New ADODB.Connection
cnn.Open glblConnectString
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "Scorecard_Delete_ATLASDataDump"
cmd.CommandType = adCmdStoredProc
cmd.Execute

This is run on my PC and I have sa capabilities on the server.

The .NET framework is installed.

Any ideas why the data access on the web page fails?you need to add the user 'MSP00427\ASPNET' to the db and giv ehim permissions. if yo xpand the databases tab on your sql server xplorer and then the database you are trying to connect to, you would see a Users tab. right click and add new user..

hth|||In SQL Server Enterprise Manager (or using scripts) you must add the 'MSP00427\ASPNET' windows user as a SQL Server user.

When you access an ASP.NET app, it is not YOUR rights that count, but the rights of the ASPNET user (by default).|||Thanks to those who responded so quickly.

I had just figured it out and tested it.

I was now coming back to post the solution for others.

You beat me to it.

Attempted Brute Force Attacks

It is not uncommon for me to review the event logs of our SQL Server and notice that someone is attempting to figure out the password for one of the SQL user accounts.......especially the "sa" username. But lately our SQL server has to be rebooted to where it is starting to become a nightly thing.

Last night the SQL Server was bombarded with attempted failed connections for 2 hours before it finally gave a blank BSOD. The SQL Server in question is in mixed mode and is a shared server. Strictly using Windows Authentication mode is not an option for us. The server has "beefy" hardware and has all updates and patches.

Sometimes, when I log on the server and notice that an IP address is making such attacks on the server, I put up an IPSEC policy against that IP. But that is not a good solution for reasons I dont think need to be outlined here.

So my question is, what can I do to better protect our SQL Server from these types of attacks? My thought on these attacks is not different than a DDOS that eventually takes the server down.

I have already done TCP/IP Hardening but not sure what else to do.

Thank you all for your replies.That sounds like Blaster to me. First order of business, patch your server, preferably to sp3a.

Next, consider killing port 1434 (and maybe 1433) at your firewall. That means that your SQL Server won't be visible outside the firewall, so this might not be a viable solution.

-PatP|||1434 is NIC Filtered as well as protected by IPSEC and Firewall.

1433 cannot be blocked becuase this is a public SQL Server used by many of our clients.

I already scanned the machine for Blaster and it is not affected. But thank you very much for your reply.|||Another solution might be to implement some form of challenge-response mechanism. For instance, if the access to the SQL Server is via a web page, have that web page (or one of its predecessors) enable the specific IP or MAC address for port 1433 for some arbitrary period of time. If the access is via a two-tier client, use an RPC to do the same thing. This will effecitvely cut off your SQL Server from all but pre-approved machines, and it is much less cumbersome for the user than using VPN to acheive similar results.

-PatP

Attaching MDF with AttachDBFilename?

I am attempting to attach an .mdf to SQL Express using "AttachDBFilename" in
the connection string, and I have a few questions on the issue.
I am planning on setting this up on a network with both the .mdf and the
instance of SQL Server residing on a server. All the clients will be runnin
g
an app that will have access to the SQL Server instance and the fileshare,
and they will attempt to use a connection string with the "AttachDBFilename"
.
I have successfully connected to the instance of SQL Server and have access
to the .mdf from a client, but I have not been able to get the connection to
attach the .mdf file. If I run the app on the server it works, but on a
client I get an error saying something like "The file '..' is on a network
path that is not supported for database files."
My questions are:
Can this be done using "AttachDBFilename"?
If yes, any suggestion on what I may be missing?
Even if I do get this method to work will it support multiple users?
Thanks,
JimThe database file (mdf) only needs to be attached one time -at the server.
Clients only need to connect to the server and have access to the database.
If SQL Express was installed on each client, and those clients used the
'AttachDBFilename', they would be attempting to attach a database file to
their local copy of SQL Express. Database files cannot be attached from a
unc network path. They would each need a local copy of the database for that
to work. However, it sounds like you want a centrally located database, not
individual copies.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:5732D62A-EE09-4ED6-8548-FFB7AC58EC2E@.microsoft.com...
>I am attempting to attach an .mdf to SQL Express using "AttachDBFilename"
>in
> the connection string, and I have a few questions on the issue.
> I am planning on setting this up on a network with both the .mdf and the
> instance of SQL Server residing on a server. All the clients will be
> running
> an app that will have access to the SQL Server instance and the fileshare,
> and they will attempt to use a connection string with the
> "AttachDBFilename".
> I have successfully connected to the instance of SQL Server and have
> access
> to the .mdf from a client, but I have not been able to get the connection
> to
> attach the .mdf file. If I run the app on the server it works, but on a
> client I get an error saying something like "The file '..' is on a network
> path that is not supported for database files."
> My questions are:
> Can this be done using "AttachDBFilename"?
> If yes, any suggestion on what I may be missing?
> Even if I do get this method to work will it support multiple users?
> --
> Thanks,
> Jim