Reality of remote connection

I'm trying to connect to my sql database across the internet from a C#
application. I am attempting to login using the administrator account that
has been added to the SQL server database and given all the roles. The auth
mode is mixed (it was windows, but I changed it to mixed after reading
around).
So, as a test I try to get Visual Studio 2003 Pro to connect to the
database, figuring it will at least do the connection string correctly. I put
in the IP of the server and the username/password into the new connection
dialog and hit Test Connection.
I get:
Test connection failed because of an error in initialising provider. Login
failed for user 'Administrator'.
So basically, I would like to know:
a) Is this possible? Should I instead be writing a server application that I
connect to on the SQL server machine that connects to SQL Server on my behalf?
b) Is there something simple I am missing? I would prefer to make a direct
connection to SQL Server if possible.
Thankyou for reading. Any help would be appreciated.
Hi
That will work, but either your username 'Administrator' or password are
wrong.
Check those and try again.
Cheers
Mike
"BLiTZWiNG" wrote:

> I'm trying to connect to my sql database across the internet from a C#
> application. I am attempting to login using the administrator account that
> has been added to the SQL server database and given all the roles. The auth
> mode is mixed (it was windows, but I changed it to mixed after reading
> around).
> So, as a test I try to get Visual Studio 2003 Pro to connect to the
> database, figuring it will at least do the connection string correctly. I put
> in the IP of the server and the username/password into the new connection
> dialog and hit Test Connection.
> I get:
> Test connection failed because of an error in initialising provider. Login
> failed for user 'Administrator'.
> So basically, I would like to know:
> a) Is this possible? Should I instead be writing a server application that I
> connect to on the SQL server machine that connects to SQL Server on my behalf?
> b) Is there something simple I am missing? I would prefer to make a direct
> connection to SQL Server if possible.
> Thankyou for reading. Any help would be appreciated.
|||Thanks Mike, I'm happy to know it can work. I even realised that I was inside
the same subnet (live proxy ip was same sub as server) so it shouldn't have
even been domain issue.
I know I have the password correct because I was remote desktoped to the
server as admin and playing with the enterprise manager. My C# / ASP.NET app
running on the same server can interact with my database, I just can't access
it from a different machine directly. The only thing I can think of is that
maybe our proxy server is not behaving, in which case I'm going to install
SQL Server on an internal server so I don't have proxy issues and try it that
way.
Thanks for your help.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> That will work, but either your username 'Administrator' or password are
> wrong.
> Check those and try again.
> Cheers
> Mike
> "BLiTZWiNG" wrote:
|||Well I got it to work. It seems that you cannot log in with a user that has
an assosciated with a windows active directory account even though the server
is in mixed mode authentication. If I log in as an SQL server user only, like
sa, it logs in fine.
Odd.
"BLiTZWiNG" wrote:
[vbcol=seagreen]
> Thanks Mike, I'm happy to know it can work. I even realised that I was inside
> the same subnet (live proxy ip was same sub as server) so it shouldn't have
> even been domain issue.
> I know I have the password correct because I was remote desktoped to the
> server as admin and playing with the enterprise manager. My C# / ASP.NET app
> running on the same server can interact with my database, I just can't access
> it from a different machine directly. The only thing I can think of is that
> maybe our proxy server is not behaving, in which case I'm going to install
> SQL Server on an internal server so I don't have proxy issues and try it that
> way.
> Thanks for your help.
> "Mike Epprecht (SQL MVP)" wrote:
|||it could possible depending on your connection-layer
(ADO,...)
that you need to set a property Use Trusted Connection to
False

>--Original Message--
>Well I got it to work. It seems that you cannot log in
with a user that has
>an assosciated with a windows active directory account
even though the server
>is in mixed mode authentication. If I log in as an SQL
server user only, like[vbcol=seagreen]
>sa, it logs in fine.
>Odd.
>"BLiTZWiNG" wrote:
realised that I was inside[vbcol=seagreen]
so it shouldn't have[vbcol=seagreen]
desktoped to the[vbcol=seagreen]
manager. My C# / ASP.NET app[vbcol=seagreen]
database, I just can't access[vbcol=seagreen]
can think of is that[vbcol=seagreen]
I'm going to install[vbcol=seagreen]
issues and try it that[vbcol=seagreen]
username 'Administrator' or password are[vbcol=seagreen]
internet from a C#[vbcol=seagreen]
administrator account that[vbcol=seagreen]
all the roles. The auth[vbcol=seagreen]
mixed after reading[vbcol=seagreen]
to connect to the[vbcol=seagreen]
connection string correctly. I put[vbcol=seagreen]
into the new connection[vbcol=seagreen]
initialising provider. Login[vbcol=seagreen]
server application that I[vbcol=seagreen]
to SQL Server on my behalf?[vbcol=seagreen]
prefer to make a direct
>.
>
|||Tried using trusted connection, but I'm not a member of the domain (as the
client using the release software will not be either).
I can live with it being in SQL native mode because it works, but I hate not
knowing why these things don't work, or if they ever can. Does my workstation
have to be a member of the domain to be able to authenticate using a windows
AD account?
"gandalf" wrote:

> it could possible depending on your connection-layer
> (ADO,...)
> that you need to set a property Use Trusted Connection to
> False
>
> with a user that has
> even though the server
> server user only, like
> realised that I was inside
> so it shouldn't have
> desktoped to the
> manager. My C# / ASP.NET app
> database, I just can't access
> can think of is that
> I'm going to install
> issues and try it that
> username 'Administrator' or password are
> internet from a C#
> administrator account that
> all the roles. The auth
> mixed after reading
> to connect to the
> connection string correctly. I put
> into the new connection
> initialising provider. Login
> server application that I
> to SQL Server on my behalf?
> prefer to make a direct
>

Reality check...

Why the "top 100 percent"? Or am I just retarded?
CREATE VIEW dbo.SmdsProductList
AS
SELECT TOP 100 PERCENT
{whole bunch of columns and expressions}
FROM
{whole bunch of joins}
WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
ORDER BY SomeOtherThing
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneIt's the only way to put an order by on a view.
"Mike Labosh" wrote:

> Why the "top 100 percent"? Or am I just retarded?
> CREATE VIEW dbo.SmdsProductList
> AS
> SELECT TOP 100 PERCENT
> {whole bunch of columns and expressions}
> FROM
> {whole bunch of joins}
> WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
> ORDER BY SomeOtherThing
>
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>
>|||'Top' is required because 'ORDER BY SomeOtherThing' has been used in the
Select Statement of the view. Here is related information from BOL (ORDER BY
clause, sorting rows):
'The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.'
Cheers and Feel happy :)
"Mike Labosh" wrote:

> Why the "top 100 percent"? Or am I just retarded?
> CREATE VIEW dbo.SmdsProductList
> AS
> SELECT TOP 100 PERCENT
> {whole bunch of columns and expressions}
> FROM
> {whole bunch of joins}
> WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
> ORDER BY SomeOtherThing
>
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>
>|||So they can put ORDER BY in the view (SQL Server won't allow you to put
ORDER BY in the VIEW unless you also specify a TOP clause).
Now why the person would need to have ORDER BY in the view, your guess is as
good as mine. Probably because they expect SELECT * FROM View to return
rows in the order they specified in the view's ORDER BY clause. But IIRC
that is not guaranteed, the optimizer is still free to return the rows in
any order it sees fit, since the actual query does not enforce any
constraint on the order (and will be more likely to in parallel operations,
which is why this behavior is seldom, if ever, planned for, when developing
on a 1-cpu box).
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:ew5LQmQ9FHA.3908@.TK2MSFTNGP10.phx.gbl...
> Why the "top 100 percent"? Or am I just retarded?
> CREATE VIEW dbo.SmdsProductList
> AS
> SELECT TOP 100 PERCENT
> {whole bunch of columns and expressions}
> FROM
> {whole bunch of joins}
> WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
> ORDER BY SomeOtherThing
>
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||In addition to what the others have answered... don't order views.
When you select from a view, then add the Order By clause.
One reason is that this is an SQL addition to the standards.
Why? because Acces allows it.
I know how much you love Acces developpers so this should be a good reason
for you not to use it.
The other is that you may want to select from the view with a different
Order By clause.
In this case, you just ordered in the View for nothing.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:ew5LQmQ9FHA.3908@.TK2MSFTNGP10.phx.gbl...
> Why the "top 100 percent"? Or am I just retarded?
> CREATE VIEW dbo.SmdsProductList
> AS
> SELECT TOP 100 PERCENT
> {whole bunch of columns and expressions}
> FROM
> {whole bunch of joins}
> WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
> ORDER BY SomeOtherThing
>
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> It's the only way to put an order by on a view.
DOH! Wow, and I've known that for years, too. Now I feel like an end-user.
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||> Why? because Acces allows it.
> I know how much you love Acces developpers so this should be a good reason
> for you not to use it.
> The other is that you may want to select from the view with a different
> Order By clause.
> In this case, you just ordered in the View for nothing.
heh. That's ok, I don't use this view anyway, I'm just trying to figure it
out. It's obviously something that somebody dragged-n-dropped together with
EM's designer. Looks like a window full of spaghetti.
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||And a nice way of inviting tempdb to the Query party.
ML|||On Tue, 29 Nov 2005 12:23:04 -0500, Mike Labosh wrote:

>Why the "top 100 percent"? Or am I just retarded?
>CREATE VIEW dbo.SmdsProductList
>AS
>SELECT TOP 100 PERCENT
> {whole bunch of columns and expressions}
>FROM
> {whole bunch of joins}
>WHERE ThisColumn = 1 AND ThatColumn IS NOT NULL
>ORDER BY SomeOtherThing
Hi Mike,
You're not retarded. This view is. Views, like tables, are by definition
NOT ordered.
ORDER BY in a VIEW is not permitted.
TOP ... ORDER BY is permitted in a view - has to be, to make sure that
the results of the TOP is predictable.
As a coincidal side-effect, SQL Server 2000 usually returns the rows in
the same order when selecting without ORDER BY from the view. Bad DBAs
and developers have abused this by using TOP 100 PERCENT ... ORDER BY in
a view.
I've already read at least one report of a view "losing it's ordering"
in SQL Server 2005. Who knows - the same might happen when the next SP
for 2000 appears.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)