Network integration for fresh install of 2005

I am working as a consultant on a project to upgrade an Access 2000 db to SQL Server. My client has bought off on everything, and they're ordering a new dedicated box this week. They're getting a rackmount machine running Windows Server and SQL Server 2005.

My expectation was that their IT people would take care of administrative & security issues such as setting the machine up, installing & starting SQL Server and configuring the proper accounts. Suprisingly though, they're going to mount the machine in the rack, turn it on and let me do all of this.

I'm new at SQL Server, but I'm comfortable working with database objects (tables, views, etc...) I'm not so comfortable with the networking or administrative side of things. It took me a couple of hours to be able to connect to the DB on my desktop machine over my home network. You can see why I'm intimidated at the prospect of getting things integrated & running on a pretty large corporate network.

Maybe I'm overblowing this, but this just gives me the willies.

Are my fears justified? Am I going to be ok if I just follow the standard procedures for allowing TCP/IP connections to the server? Am I going to have to deal with firewalls? (I think I know the answer - "it depends on their network.." - that's what I'm scared of.. I don't know anything about their network, and I don't know enough about networking in general to figure things out..)

Are there any other best practices that I should follow for a fresh install?So much depends on their network configuration that only their network people can give you a complete and correct answer to your questions. With that said, the general setup isn't difficult or complicated, the only problems I've ever run into arise when coping with installation specific problems.

Specific points to consider. If you don't know that you or your project need it, don't enable it. This especially applies to network protocols (only enable TCP/IP unless there is compelling reason for another protocol). If you don't need SQL Authentication, only select Windows Authentication.

Their network administrators are giving you a "blank check" to set things up as you'd like. This is idiocy on their part, but you should take advantage of it! Get the box running, in a minimal configuration. Set the box up as a "lone wolf" machine, then only after you are done getting things set up to suit yourself tell them you need a domain administrator login to join the box to their domain (this should put them into an outright panic), then stand back and watch the fun as they backpeddle furiously! At that point, you already have a tested, working configuration... Their only choices are to either a) give you the network equivalent of god-like powers, or b) pick up the ball that you've handed them and very quickly figure out how to run with it.

Note: This is evil, but they have brought it on themselves. The network administrators have already done one of the most stupid things that they are capable of doing, in order to force you to do all of their legwork for them. You are simply doing what they've asked, and will be presenting them with the most awful choice possible, but one that they ought to know is coming because there isn't any alternative.

-PatP|||Sorry I haven't been back for a few days..

I really don't want to do anything to create any friction with them. So far they've been fairly cooperative in allowing an outside consultant (me) to do development work for a department that's under their jurisdiction. They could easily send one email to upper management that would a) make my primary client's life miserable and b) completely cut me out of any work for them in the future.

After talking with one of their network techs yesterday, the server is up & running. It's 2005, running on a Windows Server 2003 Virtual Machine. She's going to be there, so hopefully she can take care of any network issues that arise. From what she said yesterday, they don't have any internal firewalls, so that should simplify things.

I'm going over there in a few hours to establish my login account, create a test database, and verify connectivity between it and an Access installation on an end user's machine.

Any final words of wisdom?

Network access to Sql Server 2005 Express

I have an application written in VB Express and uses SQL Server 2005 Express that runs on my local machine (name JERRY). I published it onto a CD and installed it on another computer (JKNETWORK) on my home network.

I've already modified SQL Server Configuration Manager to enable TCP/IP and Shared Memory. I have also added sqlservr.exe to the exceptions in the Microsoft firewall exceptions list.

The application opens with a login form that asks for username and password and uses the following connection string:

modUserName = txtUserName.Text

modPassWord = txtPassWord.Text

Dim ConnectionStringMaster As String =

_"Server=jerry\SQLEXPRESS;" & _

"DataBase=master;" & _

"user ID=" & modUserName & ";password=" & modPassWord

This all works great on JERRY but doesn't work from JKNETWORK. I get an error message that contains:

...When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. ....

Can someone help me figure out how to get the remote access to work?

Thanks,

JerryK

Did you enable Remote Connections? If not, see Surface Area Configuraiton tool for more info: http://msdn2.microsoft.com/en-us/library/ms161956.aspx.

|||

Hi Greg,

Yes, I did enable Remote Connections on the Surface Area Configuraiton tool. It's set for Local and Remote and with TCP/IP and Named Pipes.

If you have any other ideas, I'd certainly appreciate the help.

Thanks,

JerryK

|||

Can you access sql server on your machine from a remote machine using osql.exe? If so, then it's something with your app. If not, then it's some machine/sql configuration. Are you using winxp sp2? See if the suggestions in this thread can help. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192102&SiteID=1

|||

Greg,

I looked at the thread and have this question: What are Server Client Tools, where do I get them from to put them on the client?

I tried copying osql.exe to the client, but it looks like I need more than that because I got an error message about a missing file.

I might add that the server machine (JERRY) is a Window XP Home machine and not the Professional. But, the client machine is Windows XP Professional. Also, MSDE is still on the server machine. Could either of those circumstances have anything to do with it?

Thanks again,

JerryK

|||

Hi Jerry,

Your problem is likely caused by not having SQL Browser turned on and making an Exception for Browser in the firewall. When you are trying to connect to a named instance such as JerryK\sqlexpress, you need to have SQL Browser running on the server in order for the instance name to be recognized unless you are connecting to the server using a specific port number. Since SQL Browser lisents on it's own port, you also have to make the Exception for Browser in the firewall.

Once you've done this, you should be good to go.

Regards,
Mike Wachal
SQL Express

|||

Hi Mike,

I made sure that the browser is turned on and sqlservr.exe is in the exclude list. But, same error is reported. If I turn the Microsoft firewall off, there is no problem. If I turn on the Norton firewall it is ok too.

Are there any other settings related to the Microsoft firewall I should be concerned about?

My home network is a cable modem connected to a LinkSys router. Could there be some conflict here?

Thanks,

JerryK

|||

Jerry,

In addition to sqlservr.exe you need to add %Program Files%\Microsoft SQL Server\90\Shared\sqlbrowser.exe to the exception list.

Check out the following blog for more information: https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Cheers,
Dan

|||

Thank you Dan. That was the missing piece..

Thanks again,

jerryK

Network access SQLExpress

I am trying to access a SQLExpress database on another computer on my LAN. Using Windows Authentication. I don't really want to go to Mixed Mode authentication as I understand it involves a registry tweak and creating a new user. I am a novice at this.

Getting the following error on running the connection code below:

SQLException was unhandled "Login failed for user 'D37YKC1S\Guest'."

No doubt it is a security problem but I am not sure how to get around it. Any help appreciated.

Connection code:

Dim conn As New SqlClient.SqlConnection

conn.ConnectionString = "Server=D37YKC1S\SQLEXPRESS;Integrated Security=True;Database=VBNMDATA"

conn.Open()

Tried the following which is my Windows logon on the other computer (blank password) but same error:

conn.ConnectionString = "Server=D37YKC1S\SQLEXPRESS;Integrated Security=True;Database=VBNMDATA; User Id=Geoff; Password="

I have TCP/IP enabled in SQL Express and the firewall is off. I can see the server on the other computer with this code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

' Retrieve the enumerator instance and then the data.

Dim instance As SqlDataSourceEnumerator = _

SqlDataSourceEnumerator.Instance

Dim table As System.Data.DataTable = instance.GetDataSources()

' Display the contents of the table.

DisplayData(table)

End Sub

Private Sub DisplayData(ByVal table As DataTable)

For Each row As DataRow In table.Rows

For Each col As DataColumn In table.Columns

MessageBox.Show(col.ColumnName.ToString & " " & row(col).ToString)

Next

Next

End Sub

YOu have to create a new login for a WindowsUser you are connecting with (at this time the Guest user) and give himappropiate permissions on the database. Look for "CREATE LOGIN" in the BOL and additionally for granting rights on the db "CREATE USER) and on the appropiate objects "GRANT".

HTH, Jens Suessmeyer.

|||

Thanks. I installed BOL but it broke SQL Server Management Studio Express CTP so I will download to another machine.

Regards,

GS

|||

You don't have to change registry keys to enable mixed authentication. You can use Management Studio to do this. See instructions at: http://msdn2.microsoft.com/en-us/library/ms188670.aspx.

Could you provide more information about how BOL broke Management Studio? I suggest you also file a bug at http://lab.msdn.microsoft.com/productfeedback/Default.aspx.

Note that Books Online is also available online at: http://msdn2.microsoft.com/en-us/library/ms130214(en-us,SQL.90).aspx.

Thanks
Laurentiu

|||

Thanks again. I have VB Express and SQL Express installed along with Management Studio CTP and all working well. I installed the latest BOL and when trying to run Management Studio nothing at all happens, no error, just a blank screen. I did a search on the forums and this is not an uncommon problem and I assumed MS was aware of it. Uninstalling BOL fixed the issue. I will file a bug report as you suggested.

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=180792&SiteID=1

Regards,

GS

|||All fixed. I downloaded the latest management Studio CTP and all is well.

Nested Query Troubles...

Hi All,

Can anybody please tell me if a query such as this (Valid in MS Access)
can work in SQL Server:

SELECT Description, Sum(Total) FROM (
SELECT Description, Total FROM Table_A
UNION ALL
SELECT Description, Total FROM Table_B
UNION ALL
SELECT Description, Total FROM Table_C
)
GROUP BY Description


The group of unions work by themselves, but when I try to nest an outer query to do some a Summation(), I have syntax errors.

Any insight would be greatly appreciated. Thank you.You must supply an alias for your subquery:
SELECT Description,
Sum(Total)
FROM (SELECT Description,
Total
FROM Table_A
UNION ALL
SELECT Description,
Total
FROM Table_B
UNION ALL
SELECT Description,
Total
FROM Table_C) AS SUBQUERY
GROUP BY Description|||Thanks BlindMan, u da man.

:)

Needing something and getting something else....

Hi,

Kindly have a look at my example and do the help.

I have a database (MS Access) in which there are 10 Customers.

We have 2 products:

1) Leather Suitcase 2) Plastic Suitcase

2 Customers took Leather Suitcase & Plastic Suitcase ...while
4 Customers took only Leather Suitcase ... and
4 Customers took only Plastic Suitcase

The problem:

When I try to create a report using Crystal Reports XI to get the report to know who purchased only Leather Suitcase, I am getting all the data for 10 Customers showing all the 10 records ...?

How to solve this problem. I need only 6 rows to be shown because only 6 customers purchased the Leather Suitcase.

Please do help.

Sweetie.Use the Select Expert limit your record selection to only the customers that purchased leather

product is equal to Leather

Need Your Thoughts

I currently have an Access application that I have been working with for a
couple of years now, it is growing in size rapidly. I am thinking that I
need to be planning to take it to the next level.
Is SQL Server an appropriate thought?
Never have dealt with it before, but I have been reading a little about it.
I kinda like the thought of how it deals with records rather than Jet.
Is it appropriate to think about a web based interface to share the database
or is that thought total wrong?
I think this will be a new learning curve for me but thats alright, I thrive
on challenges.
If someone could take a few moments and en-lighten me with their thoughts it
would greatly be appriciated.
Thanks
David WI would pick Microsoft SQL Server over MSAccess any day of the w. I
can't think of any situation where I would prefer to use MSAccess. SQL
Server is a proper multi-user RDBMS (relational database management
system). It was designed like that from the ground up. Using a web
server as a front end to a SQL database is very common. There would be
literally millions of applications that do this. Companies like Barnes
& Noble (http://www.barnesandnoble.com/), Home Shopping Network
(http://www.hsn.com/) & Radio Shack (http://www.radioshack.com/) all do
this exact thing.
*mike hodgson*
http://sqlnerd.blogspot.com
David W wrote:

>I currently have an Access application that I have been working with for a
>couple of years now, it is growing in size rapidly. I am thinking that I
>need to be planning to take it to the next level.
>Is SQL Server an appropriate thought?
>Never have dealt with it before, but I have been reading a little about it.
>I kinda like the thought of how it deals with records rather than Jet.
>Is it appropriate to think about a web based interface to share the databas
e
>or is that thought total wrong?
>I think this will be a new learning curve for me but thats alright, I thriv
e
>on challenges.
>If someone could take a few moments and en-lighten me with their thoughts i
t
>would greatly be appriciated.
>Thanks
>David W
>
>|||>> I currently have an Access application that I have been working with for
a couple of years now, it is growing in size rapidly. <<
LOL!!n Welcome to developer and DBA hell! ACCESS sucks and does not
scale.
Good and probably true if the app is any good.
Yes, or another SQL product, depending on other issues.
It will be a lot of UN-learning.

Need Your Help

Hi,

I have a problem with SQL 2005, i need to access sql database as i do with MS

Access 2003.

I mean that if i can backup my database and then access it as a direct file and do the

transactions on that copy of the database without refering to SQL 2005.

Hi,

direct file access on SQL Server files is not possible. SQL Server 2005 introduced a new feature called "user instances" which will enables you to wire a data file to a SQL Server express instance, without actually attaching it as we used to in SQL Server 2000 and in prior version. You should take a deeper look in the BOL to see if that fit your needs. Be aware that you will need a SQL Server Express Service running for that on your computer.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Need urgent help about sql connection denied

From the same web server to sql server, ASP pages work fine, but .NET pages get "SQL Server does not exist or access denied" error message.
Environment:
both are Windows 2000 sp4,
sql server 2000 sp4
not firewall between
Everything is okay before I got some windows security updates fails on the sql server! and I reboot the sql server!
What happened with the security update? Why does it only effect the .NET pages not the ASP pages? They even use the same connection string!
Thanks for any comment

Are you using integrated security? That is, are you passing in a user name and password (in which case you are using SQL Server security) or not (in which case you are using integrated security). ASP and ASP.NET operate under different security contexts. ASP operates as IUSR_<machinename>, whereas ASP.NET operates under the security context of the ASPNET windows user.

I have no idea what the security update would have done.

|||

Thanks for the reply.
I do pass a user name and password in the connection string. it's the same string that I used in the ASP pages. it works fine. But I reboot the servers, only the ASP pages work.
the string looks like "DATABASE=fansion;server=dbsvr; uid=sa; pwd=password;"

|||Just tested, this problam only happens on the windows 2000 web server, windows 2003 is fine.|||

Did you ever get an answer for this re: sql connection - same string works under asp, not under asp.net, with userid= and password= and not with a dsn?

thanks a lot

Neil Jay warner

Need tools to Convert Access to SQL environment

I got a big Access file (1 400 tables) to convert to SQL and I would
like to be provided with some automated tools, except upsizing wizard
and DTS, to convert it on my own.

I got a lot of forms and query to convert too.

Can someday provide me with at least one tool name ?

Thanks,

GuyHi
AFAIK the upsizing wizard is about your only option. SQL Server does not
have forms, these will remain in the access database/project.

Have you tried the wizard? If yes does it produce any errors?

John

"Guy" <guy@.graphic-designer.com> wrote in message
news:e04bda98.0311280614.523730ea@.posting.google.c om...
> I got a big Access file (1 400 tables) to convert to SQL and I would
> like to be provided with some automated tools, except upsizing wizard
> and DTS, to convert it on my own.
> I got a lot of forms and query to convert too.
> Can someday provide me with at least one tool name ?
> Thanks,
> Guy|||Also...

You may want to post to the access news group!

John

"Guy" <guy@.graphic-designer.com> wrote in message
news:e04bda98.0311280614.523730ea@.posting.google.c om...
> I got a big Access file (1 400 tables) to convert to SQL and I would
> like to be provided with some automated tools, except upsizing wizard
> and DTS, to convert it on my own.
> I got a lot of forms and query to convert too.
> Can someday provide me with at least one tool name ?
> Thanks,
> Guy|||John,

The wizard did a little bit of the job (a lot to complete), but nothing
is available to help me finish conversion, moreover with forms and
query.

What' the Access newsgroup URL ?

Thanks,

Guy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi

Either:

comp.databases.ms-access
microsoft.public.access.*

Your forms will need to remain in the access database or an access project,
they is no equivalent in SQL server.

John

"Guy Yug" <guy@.graphic-designer.com> wrote in message
news:3fc76a10$0$88386$75868355@.news.frii.net...
> John,
> The wizard did a little bit of the job (a lot to complete), but nothing
> is available to help me finish conversion, moreover with forms and
> query.
> What' the Access newsgroup URL ?
> Thanks,
> Guy
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||On Fri, 28 Nov 2003 17:10:42 GMT, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:

>Hi
>Either:
>comp.databases.ms-access
>microsoft.public.access.*
>Your forms will need to remain in the access database or an access project,
>they is no equivalent in SQL server.
>John
>
>"Guy Yug" <guy@.graphic-designer.com> wrote in message
>news:3fc76a10$0$88386$75868355@.news.frii.net...
>>
>> John,
>>
>> The wizard did a little bit of the job (a lot to complete), but nothing
>> is available to help me finish conversion, moreover with forms and
>> query.
>>
>> What' the Access newsgroup URL ?
>>
>> Thanks,
>>
>> Guy
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>> Don't just participate in USENET...get rewarded for it!
Do you mean you want the forms / queries to remain in access but use
sql server as a source of tables, or do you want to convert them to
something else? Either way you might find this link helpful

http://www.microtools.us/

It's a tool that purports to convert an access application to an
asp.net one.|||Guy,

www.upsizewizard.com has the tool to convert Access Queries(even with
form parameters) to SQL stored procedures.

wizard|||Thanks for your time and info.,

I'll have a tour of this tool soon.

Guy

wizard@.upsizewizard.com (upsizewizard) wrote in message news:<c63ed6cf.0311281820.7f97e654@.posting.google.com>...
> Guy,
> www.upsizewizard.com has the tool to convert Access Queries(even with
> form parameters) to SQL stored procedures.
> wizard|||On 30 Nov 2003 08:57:53 -0800, guy@.graphic-designer.com (Guy) wrote:

>Thanks for your time and info.,
>I'll have a tour of this tool soon.
>Guy
>wizard@.upsizewizard.com (upsizewizard) wrote in message news:<c63ed6cf.0311281820.7f97e654@.posting.google.com>...
>> Guy,
>>
>> www.upsizewizard.com has the tool to convert Access Queries(even with
>> form parameters) to SQL stored procedures.
>>
>> wizard

Hmmmm $10.000 dollars to convert a Access Database to SQL kinda pricey
isnt it?|||Lyndon Hills <lyndon@.nospam.tenegi.com> wrote in message news:<66dfsvsdabl8u0k4baobg71df0p4fhurjs@.4ax.com>...
> Do you mean you want the forms / queries to remain in access but use
> sql server as a source of tables, or do you want to convert them to
> something else? Either way you might find this link helpful
> http://www.microtools.us/
> It's a tool that purports to convert an access application to an
> asp.net one.
Has anyone actually use this Microtools converter? Is it good?

Regards,
Michael Freidgeim|||Hi Guy,

My application generator for MS SQL Server can do the entire job for
you
The benefits of this system are as follows:
1. The system generates true Client Server and Multi Tier
applications.
2. There is zero coding for common functionality, i.e. Adds, Updates,
Deletes, Audit trail, Security, Rolling Down Data, Cascading Deletes,
Posting to General ledger etc.
3. There is zero work of any kind for generation of data entry
screens and their lookups.
4. The back end is completely independent from the front end. You can
hit the database with any application or user interface and still be
sure that you have complete security and valid data.
5. Easy navigation through out the application. The generated user
interface is a familiar modern metaphor with a navigation tree on top
or at the side and data entry screens at the bottom. Also, the
generated user interface remembers customizations to each data entry
screen. This allows you to make sweeping changes to the interface, and
regenerate all data entry screens, without loosing your
customizations.
6. Consistent look and feel via OOP Inheritance and code generators.
7. Major changes in look and functionality are made in one place
only, and ripple down to all affected parts of the system without
programmer intervention. Again, this was accomplished with OOP
inheritance and also with code generators.
8. Users to have the ability to create queries and reports on the
fly. And the ability to save and reload those queries and reports in
many formats including Excel and HTML.
9. Users to have complete flexibility in customizing the look and
feel of the system. The extent to which each user can customize the
interface must be seen to be believed. This high level of
customizability creates a high degree of user acceptance.
10. Logical use of hot keys and local popup menus allow for easy mouse
free operation, permitting the user to keep his or her hands on the
keyboard, if the user so desires.
11. All custom code added to generated data entry screens and
generated backend code persists after regeneration.
12. Comes with a business rule generator
13. Comes with a data import utility
14. Comes with a data revalidation utility to use when you change your
business rules.
Get all this without programming.
If you need to quickly build feature rich, bug free business
applications for MS SQL Server, then please call (201 665 8906) or
write to johnshearing@.aol.com|||guy@.graphic-designer.com (Guy) wrote in message news:<e04bda98.0311280614.523730ea@.posting.google.com>...
> I got a big Access file (1 400 tables) to convert to SQL and I would
> like to be provided with some automated tools, except upsizing wizard
> and DTS, to convert it on my own.
> I got a lot of forms and query to convert too.
> Can someday provide me with at least one tool name ?
> Thanks,
> Guy

1400 tables? Yikes. Is this thing set up properly? I'd make sure it
was before converting anything... Then you could use access as the
front and and SQL server as the back... but you might want to read
some of Chipman & Baron's book on the subject...|||Yo JohnShear -

You still out there?

Still offering this software?
If so please advise

BobAlston9 AT aol D O T com